CODEWITHSUNDEEP
sqlsql-server
user1742188
user1742188

Reputation: 5103

Full outer join with multiple columns

I have two tables: one containing data with end of year information and another with end of quarter information for multiple products. I want to join them so that each row has information for one product per day:

product_id | annual or quarter date | annual_info | quarterly_info

A full outer join like:

select a.date, b.date from 
annual_table a
full outer join quarterly_table q on q.product_id = a.product_id and q.date = a.date

does not work, since it only gives me the end of year dates:

date       |date       |
-----------|-----------|
1996-12-31 |           |
1997-12-31 |           |
1998-12-31 |           |
1999-12-31 |           |
2000-12-31 |           |
2001-12-31 |           |
2002-12-31 |2002-12-31 |
2003-12-31 |2003-12-31 |
2004-12-31 |2004-12-31 |
2005-12-31 |2005-12-31 |
2006-12-31 |2006-12-31 |
2007-12-31 |2007-12-31 |

I thought a full outer join was also supposed to include rows that did not have a counterpart in the other table.

Annual table:

product_id  |date       |sales            |
------------|-----------|-----------------|
GHPRRB-S-US |1996-12-31 |48.5933895113959 |
GHPRRB-S-US |1997-12-31 |177.526458150594 |
GHPRRB-S-US |1998-12-31 |184.663          |
GHPRRB-S-US |1999-12-31 |276.333          |
GHPRRB-S-US |2000-12-31 |440.285          |
GHPRRB-S-US |2001-12-31 |298.466          |
GHPRRB-S-US |2002-12-31 |301.962          |
GHPRRB-S-US |2003-12-31 |391.93           |
GHPRRB-S-US |2004-12-31 |621.138          |
GHPRRB-S-US |2005-12-31 |420.929          |
GHPRRB-S-US |2006-12-31 |517.706          |
GHPRRB-S-US |2007-12-31 |475.089          |

Quarterly table:

product_id  |date       |sales   |
------------|-----------|--------|
GHPRRB-S-US |2002-03-31 |68.97   |
GHPRRB-S-US |2002-06-30 |77.702  |
GHPRRB-S-US |2002-09-30 |80.123  |
GHPRRB-S-US |2002-12-31 |75.167  |
GHPRRB-S-US |2003-03-31 |68.97   |
GHPRRB-S-US |2003-06-30 |91.701  |
GHPRRB-S-US |2003-09-30 |103.085 |
GHPRRB-S-US |2003-12-31 |125.926 |
GHPRRB-S-US |2004-03-31 |130.28  |
GHPRRB-S-US |2004-06-30 |179.916 |
GHPRRB-S-US |2004-09-30 |163.52  |
GHPRRB-S-US |2004-12-31 |148.129 |
GHPRRB-S-US |2005-03-31 |115.843 |
GHPRRB-S-US |2005-06-30 |122.392 |
GHPRRB-S-US |2005-09-30 |124.545 |
GHPRRB-S-US |2005-12-31 |124.641 |
GHPRRB-S-US |2006-03-31 |127.33  |
GHPRRB-S-US |2006-06-30 |143.873 |
GHPRRB-S-US |2006-09-30 |136.842 |
GHPRRB-S-US |2006-12-31 |109.661 |
GHPRRB-S-US |2007-03-31 |97.253  |
GHPRRB-S-US |2007-06-30 |114.988 |
GHPRRB-S-US |2007-09-30 |128.189 |
GHPRRB-S-US |2007-12-31 |134.659 |

Upvotes: 4

Views: 8400

Answers (1)

user1742188
user1742188

Reputation: 5103

Just to close this out: I had a where clause that was restricting the results. I pushed the WHERE clause into the ON conditions and I was able to see all the results.

Thanks to @xQbert for spotting it!

Upvotes: 3

Related Questions