Return all members of a group if one member matches

Question

Post reply

Return all members of a group if one member matches

Kiara

SSCarpal Tunnel

Points: 4185
More actions
February 23, 2012 at 10:17 am

#256221

Given the following sample data:
CREATE TABLE job
( parent VARCHAR(10),
child VARCHAR(10)
)
CREATE TABLE sites
( child VARCHAR(10),
sitecode SMALLINT
)
INSERT INTO dbo.job
( parent, child )
(
SELECT 'A-1', 'A-1'
UNION ALL
SELECT 'A-1', 'A-2'
UNION ALL
SELECT 'A-1', 'A-3'
UNION ALL
SELECT 'B-1', 'B-1'
UNION ALL
SELECT 'B-1', 'B-2'
)
INSERT INTO dbo.sites
( child, sitecode )
(SELECT 'A-1', 5
UNION ALL
SELECT 'A-2', 3
UNION ALL
SELECT 'A-3', 18
UNION ALL
SELECT 'B-1', 18
UNION ALL
SELECT 'B-2', 20
)
SELECT * FROM dbo.job
SELECT * FROM dbo.sites
I need to end up with a list of all members of a "family" if any single member of the "family" matches a particular attribute.
Sample results:
for site = 3:
A-1
A-2
A-3
for site = 20:
B-1
B-2
for site = 18:
all 5 test jobs
The table structure I'm working with belongs to third party software, so I'm stuck with the existing table structure.
This is part of a much larger query, and I currently have a query that works, but I'm hoping for a better solution than the cross join that I'm currently using.
-Ki

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

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

rlswisher SSC-Addicted Points: 439 More actions · Answer 1

Here you go.

SELECT J1.Child

FROM dbo.Job J1

INNER JOIN dbo.job J on J1.Parent = J.Parent

INNER JOIN dbo.sites S on S.Child = J.Child

And sitecode = 18

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 2

I'm sure there's 100 different ways to do this, most of which are better than my solution, but here it is anyways.

select j1.child

from job j1

where j1.parent in (

select j.parent

from sites s

join job j on j.child = s.child

where s.sitecode = 18 -- Change the number here for whatever you want

)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

roryp 96873 (2/23/2012)
I'm sure there's 100 different ways to do this, most of which are better than my solution, but here it is anyways.
select j1.child
from job j1
where j1.parent in (
select j.parent
from sites s
join job j on j.child = s.child
where s.sitecode = 18 -- Change the number here for whatever you want
)

Yep, you are right: no one really would use IN ( select ...) here. Using JOIN's is the right way...:-)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Kiara SSCarpal Tunnel Points: 4185 More actions · Answer 4

Thanks. I knew I was missing something obvious - I've obviously been working on this for way too long.

Much appreciated, everyone.

-Ki