How to use SQL to query this complex structure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to use SQL to query this complex structure?

Author

Topic

TomHand
Starting Member

8 Posts

Posted - 2012-11-05 : 17:47:10

I have this table T1, it has 3 columns:
The first column is Integer, the 2nd & the 3rd is string. These are some sample data in table T1:

1 S1 A
1 B C
2 S2 A
2 B C
3 s3 C
3 B A
4 s4 K
4 K T
4 T Y
5 S5 A
5 B C
6 S6 A
6 K L
7 S7 T
7 B C

Suppose I wanna query all the groups that has this format:
? A
B C

--> so if the query executes correctly, it will show these result
1 S1 A
1 B C
2 S2 A
2 B C
5 S5 A
5 B C

The query should not show 6 or 7 as 6 or 7 only 1 row matching.

Note: the result must go with the whole group.

Can anyone show me how to form SQL to query this?
Thax u

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-05 : 19:28:11

Here is one way:

SELECT * FROM YourTable y
WHERE EXISTS
(
	SELECT * FROM YourTable y1 WHERE y1.col1 = y.col1 AND y1.col3 = 'A'
)
AND EXISTS 
(
	SELECT * FROM YourTable y2 WHERE y2.col1 = y.col1 AND y2.col2 = 'B' and y2.col3 = 'A'
)

TomHand
Starting Member

8 Posts

Posted - 2012-11-05 : 19:47:25

@ sunitabeck
****YOU SUCH GENIUS*****
*****What a BEAUTIFUL ELEGANT SIMPLE solution!!!!!!*****
I'm pondering it for many months but couldn't come up any good solution, but YOU.

Thank you very much!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources