JOIN Result

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  June 5, 2013 at 11:35 pm

  #274763

  Hi,

  yesterday I was working on a scenario in which I have 3 tables Table1, Table2, Table3. Table3 contain data from table1 and table2 so instead of writing two seperate queries I write in a singled query, but ir doesn't return any rows. I dont know why ?

  Please find the scripts below and tell me is this possible or not ?

  Create table #A (ID int identity primary key, Col1 varchar(10))

  Create table #B (ID int identity primary key, Col2 varchar(10))

  Create table #C (ID int identity primary key, Col3 varchar(10))

  INSERT INTO #A

  SELECT 'A1'

  UNION ALL

  SELECT 'A2'

  UNION ALL

  SELECT 'A3'

  INSERT INTO #B

  SELECT 'B1'

  UNION ALL

  SELECT 'B2'

  UNION ALL

  SELECT 'B3'

  INSERT INTO #C

  SELECT 'A1'

  UNION ALL

  SELECT 'B2'

  SELECT a.Col1, b.Col2

  FROM #A a

  JOIN #C C ON c.Col3 = a.Col1

  JOIN #B b ON b.Col2 = c.Col3

  DROP TABLE #A

  DROP TABLE #B

  DROP TABLE #C

  _______________________________________________________________
  To get quick answer follow this link:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Bhaskar.Shetty

  Hall of Fame

  Points: 3054

  More actions

  June 5, 2013 at 11:44 pm

  #1621593

  Becuase your query fires INNER JOIN between all three tables, So as per the test data, it will check for all Data available in all three tables.

  For Example

  First Table #A has 'A1', 'A2' and 'A3',

  Second Table #C has only one matching row in it, that's 'A1' but

  Thirds table #B has only one matching row with table #C that' 'B2' but

  'B2' not available in table #A.

  that's the reason no row returned, I think this is sufficient explanatory.

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  June 6, 2013 at 2:05 am

  #1621626

  What is the result you are expecting based on your sample data?

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Jason-299789

  SSC-Insane

  Points: 21601

  More actions

  June 6, 2013 at 8:55 am

  #1621775

  It looks like table 3 (#C) might be better placed as a TUPLE table where by you have two columns one referencing Table A, the other Table B.

  You would then load Intersecting data into Table C, eg,

  A1, B2

  A2, B3

  A3, B1

  This would then allow you to use the query you have.

  One word of warning I would make the two Columns in table C a compund PK, so that you can only keep one combination of each pair to prevent duplications occuring.

  _________________________________________________________________________
  SSC Guide to Posting and Best Practices