SQL syntax help - this should be simple

Question

SQL syntax help - this should be simple

mw_sql_developer

SSCoach

Points: 19445
More actions
September 3, 2015 at 8:54 am

#398991

I need the sql statement that meets the following
I need a list of memberids who meet the following criteria
Select memberid, measureid from numerator
1. There should be atleast 1 record where the measureid = 501
2. There should be atleast 1 record where the measureid = 502
3. There should be atleast 1 record where the measureid = 503

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

mw112009 (9/3/2015)
I need the sql statement that meets the following
I need a list of memberids who meet the following criteria
Select memberid, measureid from numerator
1. There should be atleast 1 record where the measureid = 501
2. There should be atleast 1 record where the measureid = 502
3. There should be atleast 1 record where the measureid = 503

SELECT somecolumns

FROM sometable

WHERE something equals something else (or doesn't)

If you provide a little sample data, someone will fill in the gaps.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

It could be with something like this:

CREATE TABLE numerator(

memberid int,

measureid int

);

INSERT INTO numerator

VALUES

(1,501),

(1,502),

(1,503),

(2,501),

(2,502),

(3,501),

(4,501),

(4,502),

(4,503),

(4,504),

(4,505);

SELECT memberid,

measureid

FROM numerator

WHERE memberid IN (

SELECT memberid

FROM numerator

GROUP BY memberid

HAVING COUNT( DISTINCT CASE WHEN measureid IN( 501, 502, 503) THEN measureid END)= 3);

GO

DROP TABLE numerator;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sgmunson SSC Guru Points: 110639 More actions · Answer 3

Luis Cazares (9/3/2015)
It could be with something like this:
CREATE TABLE numerator(
memberid int,
measureid int
);
INSERT INTO numerator
VALUES
(1,501),
(1,502),
(1,503),
(2,501),
(2,502),
(3,501),
(3,501),
(3,501),
(3,501),
(4,501),
(4,502),
(4,503),
(4,504),
(4,505);
SELECT memberid,
measureid
FROM numerator
WHERE memberid IN (
SELECT memberid
FROM numerator
GROUP BY memberid
HAVING COUNT( DISTINCT CASE WHEN measureid IN( 501, 502, 503) THEN measureid END)= 3);
GO
DROP TABLE numerator;

Based solely on the original post, only the GROUP BY portion of your query is actually needed:

CREATE TABLE #numerator(

memberid int,

measureid int

);

INSERT INTO #numerator

VALUES (1,501),

(1,502),

(1,503),

(2,501),

(2,502),

(3,501),

(4,501),

(4,502),

(4,503),

(4,504),

(4,505);

SELECT memberid

FROM #numerator

GROUP BY memberid

HAVING COUNT(DISTINCT CASE WHEN measureid IN(501, 502, 503) THEN measureid END)= 3--);

GO

DROP TABLE #numerator;

GO

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

sgmunson (9/8/2015)
Based solely on the original post, only the GROUP BY portion of your query is actually needed:

It depends on the expected output. My code will return all the rows while yours will only return the 2 memberid.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sgmunson SSC Guru Points: 110639 More actions · Answer 5

Luis Cazares (9/8/2015)
sgmunson (9/8/2015)
Based solely on the original post, only the GROUP BY portion of your query is actually needed:
It depends on the expected output. My code will return all the rows while yours will only return the 2 memberid.

Ummm... that was the stated objective in the original post...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

mw_sql_developer SSCoach Points: 19445 More actions · Answer 6

mw_sql_developer

SSCoach

Points: 19445

September 8, 2015 at 4:13 pm

#1825291

cool

good job.

i am happy with the reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

sgmunson (9/8/2015)
Luis Cazares (9/8/2015)
sgmunson (9/8/2015)
Based solely on the original post, only the GROUP BY portion of your query is actually needed:
It depends on the expected output. My code will return all the rows while yours will only return the 2 memberid.
Ummm... that was the stated objective in the original post...

:ermm: Somehow I missed that part. 😀

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

mw112009 (9/8/2015)
cool
good job.
i am happy with the reply

Now... what did you learn?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)