DEMO_CODE = 'DE1'

Question

Post reply

DEMO_CODE = 'DE1'

RVO

SSCertifiable

Points: 5495
More actions
May 7, 2014 at 6:12 am

#288539

Hi everyone.
I have these two records in [prj_demo_data] table:
prj_code demo_code decmo_vchar_data
---------------------------------------------
1425 DE1 U
1425 RAP YES
1426 DE1 B
How do I select only those records where the same project has both:
demo_code = 'DE1' and demo_vchar_data = 'U'
and
demo_code = 'RAP' and demo_vchar_data = 'YES'

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

SELECT [stuff]

FROM [thing]

WHERE

(demo_code = 'DE1' AND demo_vchar_data = 'U')

OR

(demo_code = 'RAP' AND demo_vchar_data = 'YES')

^{“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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

SELECT *

FROM MyTable WHERE prj_code IN

(

SELECT prj_code

FROM MyTable

WHERE demo_code = 'DE1' and demo_vchar_data = 'U'

INTERSECT

SELECT prj_code

FROM MyTable

WHERE demo_code = 'RAP' and demo_vchar_data = 'YES'

)

AND demo_code IN ('DE1','RAP') AND demo_vchar_data IN ('U','YES');

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

RVO SSCertifiable Points: 5495 More actions · Answer 3

God Bless you , Koen Verbeeck !

First I thought what an easy task...and then I felt embarrassed when after 3 hours I was still not able

to select those pairs.

I need to read about this INTERSECT to understand how it works...

Thank you so much!

RVO SSCertifiable Points: 5495 More actions · Answer 4

Hello Koen,

I just noticed this returns the same results.

Do I really need this last line ?

SELECT *

FROM prj_demo_data

WHERE prj_code IN

(

SELECT prj_code

FROM prj_demo_data

WHERE demo_code = 'DE1' and demo_data_vchar = 'U'

INTERSECT

SELECT prj_code

FROM prj_demo_data

WHERE demo_code = 'RAP' and demo_data_vchar = 'YES'

)

--code works even without this line?...

--AND demo_code IN ('DE1','RAP') AND demo_data_vchar IN ('U','YES')

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

For the test data you provided, the last line is not necessary. I added it as an extra check.

Say for example project 1425 has a third line with demo_code DE2, than this extra row is not returned.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP