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

• rlswisher

  SSC-Addicted

  Points: 439

  More actions

  February 23, 2012 at 10:29 am

  #1450998

  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

  February 23, 2012 at 10:37 am

  #1451003

  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

  February 23, 2012 at 10:46 am

  #1451020

  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

  February 23, 2012 at 11:23 am

  #1451044

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

  Much appreciated, everyone.

  -Ki