join tables on multiple criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

join tables on multiple criteria

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2013-10-14 : 11:08:54

Hi,

I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b.

Here is table a:
naics ust_code port all_qty_1_yr all_qty_2_yr all_val_yr all_air_val_yr all_air_wgt_yr all_ves_val_yr all_ves_wgt_yr all_cnt_val_yr all_cnt_wgt_yr all_border_val_yr
11111000 2010 2002 8070569.14298579 0 2335641254.30021 0 0 2335641254.30021 8156408492.66667 0 0 0
11111000 2230 2010 280841.478063446 0 84622385.9133129 0 0 84622385.9133129 299600780.773355 0 0 0
11111000 2410 1401 25735 0 12305667 0 0 12305667 25719794 0 0 0
11111000 3330 1401 173 0 60820 0 0 60820 173020 35258 61020 0
11111000 3570 1303 16 0 6090 0 0 6090 16738 6090 16738 0
11111000 4840 2002 192000 0 54232320 0 0 54232320 192000000 0 0 0
11111000 5170 2002 38860 0 15063318 0 0 15063318 38860260 0 0 0
11111000 5490 1401 32845 0 9673200 0 0 9673200 32669142 0 0 0
11111000 5490 3604 226 0 75000 0 0 0 0 0 0 75000
11111000 5590 1401 633 0 306808 0 0 306808 635310 306808 635310 0

and here is table b:

naics ust_code port stat_month Cum_qty_1_mo Cum_qty_2_mo Cum_all_val_mo Cum_air_val_mo Cum_air_wgt_mo Cum_ves_val_mo
11111000 1220 0106 01 2 0 3440 0 0 0
11111000 1220 0107 03 14 0 3442 0 0 0
11111000 1220 0108 09 0 0 0 0 0 0
11111000 1220 0109 08 0 0 0 0 0 0
11111000 1220 0127 05 14 0 3499 0 0 0
11111000 1220 0212 12 323 0 107137 0 0 0
11111000 1220 0701 01 2 0 3513 0 0 0
11111000 1220 0708 08 24 0 7814 0 0 0
11111000 1220 0712 08 24 0 7814 0 0 0
11111000 1220 0715 12 207 0 60110 0 0 0

I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’

/****** Script for SelectTopNRows command from SSMS ******/
SELECT a.[naics]
,a.[ust_code]
,a.[port]
,a.[all_qty_1_yr]
,a.[all_qty_2_yr]
,a.[all_val_yr]
,a.[all_air_val_yr]
,a.[all_air_wgt_yr]
,a.[all_ves_val_yr]
,a.[all_ves_wgt_yr]
,a.[all_cnt_val_yr]
,a.[all_cnt_wgt_yr]
,a.[all_border_val_yr]
,b.[stat_month]
,b.[Cum_qty_1_mo] [Cum_qty_1_mo]_Jan --(Feb, ...)
,b.[Cum_qty_2_mo] [Cum_qty_2_mo]_Jan --(Feb, ...)
,b.[Cum_all_val_mo] [Cum_all_val_mo]_Jan --(Feb, ...)
,b.[Cum_air_val_mo] [Cum_air_val_mo]_Jan --(Feb, ...)

FROM [trade].[dev].[8DigitsNaicsValueWeightShare_PORTS_exp] a

left join

( SELECT [naics]
,[ust_code]
,[port]
,[stat_month]
,[Cum_qty_1_mo]
,[Cum_qty_2_mo]
,[Cum_all_val_mo]
,[Cum_air_val_mo]
,[Cum_air_wgt_mo]
,[Cum_ves_val_mo]

FROM [trade].[dev].[8DigitsNaicsCumMonth_PORTS_exp]
) b

on
a.[naics] = b.naics
and a.ust_code = b.ust_code
and a.port = b.port

where b.stat_month = '01' --Jan

left join

where b.stat_month = '02' – Feb

*****
output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like

* columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)

Thank you

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-14 : 12:00:12

It is very hard for someone to read the unaligned data and understand the logic you are trying to implement. It would help if you can create sample data in tables that can be copied and used by someone to write a query against. I created the first table for you - see below. If you do similar things for your required output and write your existing queries against the sample data you create, then you will get quicker and more accurate answers:

CREATE TABLE #A(
	naics INT,
	ust_code INT,
	port INT,
	all_qty_1_yr FLOAT,
	all_qty_2_yr FLOAT,
	all_val_yr FLOAT,
	all_air_val_yr FLOAT,
	all_air_wgt_yr FLOAT,
	all_ves_val_yr FLOAT,
	all_ves_wgt_yr FLOAT,
	all_cnt_val_yr FLOAT,
	all_cnt_wgt_yr FLOAT,
	all_border_val_yr FLOAT
);

INSERT INTO #A values ('11111000','2010','2002','8070569.14298579','0','2335641254.30021','0','0','2335641254.30021','8156408492.66667','0','0','0');
INSERT INTO #A values ('11111000','2230','2010','280841.478063446','0','84622385.9133129','0','0','84622385.9133129','299600780.773355','0','0','0');
INSERT INTO #A values ('11111000','2410','1401','25735','0','12305667','0','0','12305667','25719794','0','0','0');
INSERT INTO #A values ('11111000','3330','1401','173','0','60820','0','0','60820','173020','35258','61020','0');
INSERT INTO #A values ('11111000','3570','1303','16','0','6090','0','0','6090','16738','6090','16738','0');
INSERT INTO #A values ('11111000','4840','2002','192000','0','54232320','0','0','54232320','192000000','0','0','0');
INSERT INTO #A values ('11111000','5170','2002','38860','0','15063318','0','0','15063318','38860260','0','0','0');
INSERT INTO #A values ('11111000','5490','1401','32845','0','9673200','0','0','9673200','32669142','0','0','0');
INSERT INTO #A values ('11111000','5490','3604','226','0','75000','0','0','0','0','0','0','75000');
INSERT INTO #A values ('11111000','5590','1401','633','0','306808','0','0','306808','635310','306808','635310','0');

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources