3

I am trying to write a join statement to join the following three data sets together. (This is using MS SQL Server)

Set 1
ID    Date    Col1 
1     Jan 11  a1 
1     Jan 13  a2

Set 2
ID    Date    Col2 
1     Jan 11  b1 
1     Jan 15  b2

Set 3
ID    Date    Col3
1     Jan 15  c1 
1     Jan 17  c2

Combined Set
ID    Date    Col1    Col2    Col3
1     Jan 11  a1      b1
1     Jan 13  a2
1     Jan 15          b2       c1
1     Jan 17                   c2

I think a full outer join is able to do this but I am running into major cross product issues.

John Soer
  • 551
  • 1
  • 7
  • 20
  • what major cross product issues are you running into? – Jensen Ching Nov 19 '12 at 04:21
  • 2
    This data structure & combined data is confusing. ID is the same across all rows? What is the FK that relates these Sets? What have you tried & what issue are you receiving? – Jeremy Morehouse Nov 19 '12 at 04:25
  • Agree. I think he doesn't need the ID in the result table, though. – Mosty Mostacho Nov 19 '12 at 04:34
  • To add some details. I just included one ID in fact there are many IDs. I am looking at medical data so the ID is the PatientID. Set 1 is prescription so the date is the medication start date. Set 2 are lab results so the date is the Observation date. Set 3 are appointments the date is the appointment date. I am trying to get a complete set of data to generate some reports. – John Soer Nov 19 '12 at 05:35

3 Answers3

3

Give this a try:

select coalesce(t1.date, t2.date, t3.date) date, col1, col2, col3 from table1 t1
full outer join table2 t2 on (t1.date = t2.date)
full outer join table3 t3 on (t2.date = t3.date)
Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
1

Based on your dataset, you seem like you want to join the 3 tables based on the date column. Note that I'm disregarding the ID column here, until you clarify why all the IDs are set to 1.

SELECT ISNULL(set1.dt, ISNULL(set2.dt, set3.dt)) as 'Date', col1, col2, col3
FROM set1
FULL OUTER JOIN set2 ON CAST(set1.dt AS DATE) = CAST(set2.dt AS DATE)
FULL OUTER JOIN set3 ON CAST(set2.dt AS DATE) = CAST(set3.dt AS DATE)
Jensen Ching
  • 3,144
  • 4
  • 26
  • 42
1

SELECT ISNULL(a.ID,isnull(b.ID,c.ID)) ID,coalesce(a.dt, b.dt, c.dt) , Col1, Col2, Col3 FROM set1 a FULL OUTER JOIN set2 b ON a.dt = b.dt FULL OUTER JOIN set3 c ON b.dt= c.dt

Patrick Guimalan
  • 990
  • 9
  • 11