SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to use SQL to query this complex structure?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TomHand
Starting Member

Australia
8 Posts

Posted - 11/05/2012 :  17:47:10  Show Profile  Reply with Quote
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

Edited by - TomHand on 11/05/2012 17:58:47

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/05/2012 :  19:28:11  Show Profile  Reply with Quote
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'
)
Go to Top of Page

TomHand
Starting Member

Australia
8 Posts

Posted - 11/05/2012 :  19:47:25  Show Profile  Reply with Quote
@ 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!!!

Edited by - TomHand on 11/05/2012 19:48:08
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000