4 Tables Join with comman column

• syed_3177

  SSC Enthusiast

  Points: 137

  More actions

  February 10, 2016 at 12:09 am

  #315814

  I have 4 tables one comman column (Date and Qty) and another field

  I want to join 4 tables with date as comman column.Some times qty will be null for particular date. I want to display all the dates even when one of table qty is Null.

  Further details provided.

  Thanks

• djj

  SSCoach

  Points: 18831

  More actions

  February 10, 2016 at 5:10 am

  #1857656

  syed_3177 (2/10/2016)

  ---

  I have 4 tables one comman column (Date and Qty) and another field

  I want to join 4 tables with date as comman column.Some times qty will be null for particular date. I want to display all the dates even when one of table qty is Null.

  Further details provided.

  Thanks

  Look at LEFT JOIN. Since no information on the tables was given I do not know where to start.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 10, 2016 at 5:21 am

  #1857663

  syed_3177 (2/10/2016)

  ---

  ....Further details provided.

  Thanks

  Further details will need to be provided! Minimum requirements:

  CREATE TABLE scripts for all tables.

  INSERT scripts to populate those tables with a few rows of data.

  A script to generate the expected output from that data.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• syed_3177

  SSC Enthusiast

  Points: 137

  More actions

  February 10, 2016 at 6:20 am

  #1857684

  I need in this output format

  table1(Date,A)

  01-01-2016 10

  02-01-2016 12

  table2(Date,B)

  01-01-2016 9

  03-01-2016 5

  table3(Date,C)

  01-01-2016 7

  02-01-2016 8

  table4(Date,D)

  02-01-2016 5

  03-01-2016 5

  Desired result

  Date A B C D

  01-01-2016 10 9 7 5

  02-01-2016 12

  03-01-2016 5 8 5

  Hope this helps

  Thanks

• HanShi

  SSC-Dedicated

  Points: 33507

  More actions

  February 10, 2016 at 6:42 am

  #1857694

  create table table1(Date date,A int)

  insert into table1 values('01-01-2016', 10)

  insert into table1 values('02-01-2016', 12)

  create table table2(Date date,B int)

  insert into table2 values('01-01-2016', 9)

  insert into table2 values('03-01-2016', 5)

  create table table3(Date date,C int)

  insert into table3 values('01-01-2016', 7)

  insert into table3 values('02-01-2016', 8)

  insert into table3 values('05-01-2016', 8)

  create table table4(Date date,D int)

  insert into table4 values('02-01-2016', 5)

  insert into table4 values('03-01-2016', 5)

  insert into table4 values('04-01-2016', 5)

  -- select each available [Date] value from all tables combined

  ;WITH CTE_Date AS(SELECT [Date] FROM Table1

  UNION

  SELECT [Date] FROM Table2

  UNION

  SELECT [Date] FROM Table3

  UNION

  SELECT [Date] FROM Table4

  )

  -- outer join the [Date] list with each table

  SELECT CTE_Date.[Date], [A], , [C], [D]

  FROM CTE_Date

  LEFT OUTER JOIN Table1

  ON CTE_Date.[Date] = table1.[Date]

  LEFT OUTER JOIN Table2

  ON CTE_Date.[Date] = table2.[Date]

  LEFT OUTER JOIN Table3

  ON CTE_Date.[Date] = table3.[Date]

  LEFT OUTER JOIN Table4

  ON CTE_Date.[Date] = table4.[Date]

  drop table table1

  drop table table2

  drop table table3

  drop table table4

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **