Joining tables - SQL Server Forums

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

Joining tables

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2013-10-10 : 12:06:17

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. I want to keep all the records in a, and join columns from b. I do not want to loose any row from a if there is no data for that row in b.

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: (Your help would be appreciated)

/****** 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
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-10 : 14:10:15

first show us how your data is.
From your explanation it seems like what you need is some kind of pivoting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

goligol
Posting Yak Master

128 Posts

Posted - 2013-10-10 : 14:52:21

Thank you so much:

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-11 : 02:34:51

and what should be your output?
I cant see any month columns in table a. It says 1_yr etc so does that represent months?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

goligol
Posting Yak Master

128 Posts

Posted - 2013-10-11 : 10:01:02

No, table a is yearly information, what I want to do I want tho have break out column from table b to accommodate months:
output should have all columns from a and join collumns 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)

Thnak you

goligol
Posting Yak Master

128 Posts

Posted - 2013-10-14 : 09:41:37

Any comments?????

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-14 : 09:45:31

quote:
Originally posted by goligol

Any comments?????

Nope. As of now I cant make out anything from your description on whats your exact scenario.
Learn to post with proper information and then somebody might be able to help you.

see guidelines here
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources