SQL Join Question

• vision59

  SSCrazy

  Points: 2601

  More actions

  November 29, 2009 at 12:13 pm

  #214412

  I have a query where I need to get the data joining two tables. Ex I have table a table b.

  I need count(*) of records.

  I have my query like this.

  select count(*) from table a

  left outer join table b on a.aid = b.bid

  There are records which may not have a matching id but they should be matched on type.

  EX

  select count(*) from table a

  left outer joinb table b on a.type = b.type

  how can i acheive joining table b to include two different columns

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  November 29, 2009 at 3:06 pm

  #1085179

  vision59

  This seems to work - but without your data/table structure and very, very limited testing on my part:

  CREATE TABLE #Ta(aid INT,Type VARCHAR(3))

  INSERT INTO #Ta

  SELECT 1, 'AB' UNION ALL

  SELECT 1, 'AC' UNION ALL

  SELECT 2, 'BX' UNION ALL

  SELECT 7, 'AX' UNION ALL

  SELECT 8, 'AX'

  CREATE TABLE #Tb(aid INT,Type VARCHAR(3))

  INSERT INTO #Tb

  SELECT 1, 'bB' UNION ALL

  SELECT 1, 'bC' UNION ALL

  SELECT 3, 'BX'

  select count(*) from #Ta Tc

  left outer join #Tb Td ON (Tc.aid = Td.aid)

  right join #Tb Te ON (Tc.aid <> Te.Aid AND Tc.Type = Te.Type)

  Returns a count = 3

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]