Table Join on three columns

Question

Post reply

Table Join on three columns

bawinkley

SSC Enthusiast

Points: 126
More actions
July 18, 2013 at 12:07 am

#296492

I have two tables with identical structure.
one table is for month1 the other is for month2
i want to compare the two.
the data is issued to us and i am trying to report on the differences.
I am trying to compare based on a group of columns Level, code, and area.
I cant trust that the groups will be the same in both tables so i'll probably add another table with the unique list of the groupings for each table after wards and use the method to get the Qty from both tables based on the group of columns.
i have attached an image to help explain.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

Perhaps you can do it without the third table:

select

coalesce(june.level, july.level) as 'level'

, coalesce(june.sthdi_code, july.sthdi_code) as 'sthdi_code'

, coalesce(june.Functional_Area, july.Functional_Area) as 'Functional_Area'

, coalesce(june.Quantity, 0) as 'Qty_june'

, coalesce(july.Quantity, 0) as 'Qty_july'

, coalesce(july.Quantity, 0) - coalesce(june.Quantity, 0) as 'Difference'

from june

full outer join july

on june.level = july.level

and june.sthdi_code = july.sthdi_code

and june.Functional_Area = july.Functional_Area

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

bawinkley SSC Enthusiast Points: 126 More actions · Answer 2

that works perfectly thanks for your help! I need to do some further research to understand how it works but that's excellent.

Cheers

HanShi SSC-Dedicated Points: 33506 More actions · Answer 3

Explanation of the code:

Both tables (june and july) are joined on the matching columns. Because it's a FULL OUTER join, all the records will be in the result, even if there is no matching row in the other table. If there is no matching row, the missing columns in the result will be filled with NULL values. In the code both columns are combined into a single column. By using the COALESCE function, the first non-NULL value is displayed.

To calculate the difference a COALESCE function is used to convert the NULL values to the number 0.

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

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 4

An alternative solution...

;WITH cteJune ([level],[SthdiCode],[FunctionalArea],[Qty])

AS

(

SELECT '01','G22','Area1',5 UNION ALL

SELECT '01','G22','Area2',5 UNION ALL

SELECT '01','G22','Area3',5 UNION ALL

SELECT '02','G22','Area1',5 UNION ALL

SELECT '02','G22','Area2',5 UNION ALL

SELECT '02','G22','Area3',20 UNION ALL

SELECT '02','WWW','Area3',5

),

cteJuly ([level],[SthdiCode],[FunctionalArea],[Qty])

AS

(

SELECT '01','G22','Area1',15 UNION ALL

SELECT '01','G22','Area2',15 UNION ALL

SELECT '01','G22','Area3',15 UNION ALL

SELECT '02','G22','Area1',15 UNION ALL

SELECT '02','G22','Area2',15 UNION ALL

SELECT '02','G22','Area3',15 UNION ALL

SELECT '02','WWW','Area3',5

)

SELECT

r.[level]

,r.SthdiCode

,r.FunctionalArea

,r.QtyJune

,r.QtyJuly

,SUM(r.QtyJuly-r.QtyJune) OVER (PARTITION BY r.[level],r.SthdiCode,r.FunctionalArea) AS [Difference]

FROM

(

SELECT DISTINCT

cteJune.[level]

,cteJune.SthdiCode

,cteJune.FunctionalArea

,cteJune.Qty AS QtyJune

,cteJuly.Qty AS QtyJuly

FROM

cteJune

INNER JOIN

cteJuly

ON cteJune.[level] = cteJuly.[level]

AND cteJune.SthdiCode = cteJuly.SthdiCode

AND cteJune.FunctionalArea = cteJuly.FunctionalArea

) r

levelSthdiCodeFunctionalAreaQtyJuneQtyJulyDifference

01G22Area151510

01G22Area251510

01G22Area351510

02G22Area151510

02G22Area251510

02G22Area32015-5

02WWWArea3550