Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DEMO_CODE = 'DE1' Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2014 6:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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'
Post #1568404
Posted Wednesday, May 7, 2014 6:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1568407
Posted Wednesday, May 7, 2014 6:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 13,570, Visits: 11,383
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');





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1568408
Posted Wednesday, May 7, 2014 6:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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!
Post #1568414
Posted Friday, May 9, 2014 4:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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')

Post #1569198
Posted Friday, May 9, 2014 4:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 13,570, Visits: 11,383
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1569201
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse