asked on

Sql Query using the IN clause..

I have a table.. we'll call Family.
In the table I have these records

XTYPE ID Nme      OtherStuff
---- ----- ------- --------------
MST 01 Clan1      bla bla bla
SON 01 Clan1 bla XXX YYY
OTH 01 Clan1      ZZZ sdfafaf
MST 02 Clan2      asdfafafaf
OTH 02 Clan2 a2334141414
MST 03 Clan3      jwj3j3j3j3j3
SON 03 Clan3      uuuu bbbb cccc

What I am trying to accomplish is this:
I want to get all the "othestuff" data fields (there are really
more than just one) for each MST based upon the presnt of the 'SON'
entry.

So eventuly my query would return
MST 01 Clan1      bla bla bla
MST 03 Clan3      jwj3j3j3j3j3

because of the presence of the SON entry.

How can I code this in SQL? I tried doing it with a

Select * From Faimly
Where XTYPE IN(Select * From Family Where XTYPE='SON')

But that didn't return the expected results.

ASKER CERTIFIED SOLUTION

Simon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sara bhai

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

No points please.
Just want to report an error in sarabhai's solution. He swapped 'MST' with 'SON' so the correct should be:

;with family 
 AS
 (
 SELECT * FROM #Family WHERE XTYPE = 'SON'
 )
 SELECT * FROM #family WHERE ID IN (SELECT ID FROM family) AND XTYPE = 'MST'

Open in new window

SOLUTION

John_Vidmar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

datafarmer

ASKER

I accepted the first one, because it was a basic flaw in my original query.