Left Outer join on multiple columns same table

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

SQL Server 2005 Forums

Transact-SQL (2005)

Left Outer join on multiple columns same table

Author

Topic

ayanafridi
Starting Member

7 Posts

Posted - 2010-02-23 : 10:30:44

Hi,

I have two tables:
Table1: id, value, valuenum
Table2: id, valuenum (where id and valuenum form the PK)

In table 1, there are rows such as:


id   |   value  |  valuenum
1        ABC         1
1        XYZ         3
1        HSH         4   
2        sdg         1
2        ruv         3
2        pop         4

Table 2 has rows:
id   |  valuenum
1         1
1         2
1         3
1         4
2         1
2         2
2         3
2         4

I am trying to do a join on these two tables, so that I get results like this:


id | value | valuenum
1        ABC         1
1        null        3
1        XYZ         3
1        HSH         4   
2        sdg         1
1        null        2
2        ruv         3
2        pop         4

Here is my current query:


Select tbl1.id, tbl1.value, tbl2.valuenum
from Table1 tbl1
left outer join table2 tbl2 on tbl2.id = tbl1.id and tbl2.valuenum = tbl1.valuenum

However, this does not return the values above in red. Any idea on how to solve this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 10:33:20

[code]Select tbl2.id, tbl1.value, tbl2.valuenum
from table2 tbl2
left outer join Table1 tbl1 on tbl2.id = tbl1.id and tbl2.valuenum = tbl1.valuenum
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ayanafridi
Starting Member

7 Posts

Posted - 2010-02-23 : 10:41:43

quote:
Originally posted by visakh16

Select tbl2.id, tbl1.value, tbl2.valuenum
from table2 tbl2
left outer join Table1 tbl1 on tbl2.id = tbl1.id and tbl2.valuenum = tbl1.valuenum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

That doesn't seem to work and produces the same results. How is your query different from mine, besides switching the from/join tables?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 10:54:55

My query gives your sample output. see



 
SELECT * INTO #tbl1
from
(
SELECT 1 as id,        'ABC' as value,         1 as valuenum union all
SELECT 1,        'XYZ' ,        3 union all
SELECT 1,        'HSH' ,        4    union all
SELECT 2,        'sdg',         1 union all
SELECT 2,        'ruv',        3 union all
SELECT 2,        'pop',         4
)t


  
SELECT * INTO #tbl2
FROM
(
SELECT 1 AS id,         1 AS valuenum UNION ALL 
SELECT 1,         2 UNION ALL
SELECT 1,         3 UNION ALL
SELECT 1,         4 UNION ALL
SELECT 2,         1 UNION ALL
SELECT 2,         2 UNION ALL
SELECT 2,         3 UNION ALL
SELECT 2,         4
)t1

Select tbl2.id, tbl1.value, tbl2.valuenum
from #tbl2 tbl2
left outer join #tbl1 tbl1 on tbl2.id = tbl1.id and tbl2.valuenum = tbl1.valuenum


drop table #tbl1
drop table #tbl2

output
--------------------------------
id	value	valuenum
1	ABC	1
1	NULL	2
1	XYZ	3
1	HSH	4
2	sdg	1
2	NULL	2
2	ruv	3
2	pop	4

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 10:58:10

Ok just noticed your output. Can i ask how you got valuenum as 3 for record with null value and id 1?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ayanafridi
Starting Member

7 Posts

Posted - 2010-02-23 : 11:13:05

quote:
Originally posted by visakh16

Ok just noticed your output. Can i ask how you got valuenum as 3 for record with null value and id 1?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry this should be "2":


id | value | valuenum
1        ABC         1
1        null        2
1        XYZ         3
1        HSH         4   
2        sdg         1
1        null        2
2        ruv         3
2        pop         4

I tried your code and it works but for some reason when applied to my tables it does not produce the correct results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 11:14:47

what?? why doesnt it work on your tables? are you sure what you've shown here is the actual data in tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ayanafridi
Starting Member

7 Posts

Posted - 2010-02-23 : 12:23:14

It's a representation of the data - unfortunately the data is really hard to represent, so I tried here, not sure if i've succeeded. Also it is sensitive, so I can't really show it.

The only main difference I can tell is that in table 1, there are several fields which are represented by "id":
metric_id/institution_id/period_id

This is table1 DDL


CREATE TABLE [dbo].[MTS_DATA_PRELOAD](
	[temp_metric_id] [numeric](5, 0) NULL,
	[metric_id] [numeric](5, 0) NULL,
	[year] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[term_name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[temp_institution_short_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[institution_short_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[value] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[unit_id] [numeric](2, 0) NULL,
	[currency_id] [numeric](2, 0) NULL,
	[source] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[section] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[page] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[data_comment] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[value_number] [numeric](3, 0) NULL,
	[risk_department_id] [numeric](3, 0) NULL,
	[risk_area_id] [numeric](5, 0) NULL,
	[period_id] [numeric](7, 0) NULL,
	[institution_id] [numeric](10, 0) NULL
) ON [PRIMARY]

INSERT INTO [smts_temp_mdk].[dbo].[MTS_DATA_PRELOAD]
           ([temp_metric_id]
,[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id]
,[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id]
,[period_id],[institution_id])
     VALUES
           (<temp_metric_id, numeric,>,<metric_id, numeric,>
,<year, varchar(4),>,<term_name, varchar(30),>,<temp_institution_short_name, varchar(50),>,<institution_short_name, varchar(50),>
,<value, varchar(100),>,<unit_id, numeric,>,<currency_id, numeric,>,<source, varchar(200),>,<section, varchar(100),>
,<page, varchar(10),>,<data_comment, varchar(2000),>,<value_number, numeric,>,<risk_department_id, numeric,>
,<risk_area_id, numeric,>,<period_id, numeric,>,<institution_id, numeric,>)

Some inserts:


INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','ABC',NULL,'4',NULL,NULL,NULL,NULL,'1','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','XYZ CAP',NULL,'4',NULL,NULL,NULL,NULL,'2','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','NBFI',NULL,'4',NULL,NULL,NULL,NULL,'3','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','xyzd cap',NULL,'4',NULL,NULL,NULL,NULL,'4','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','NBFI',NULL,'4',NULL,NULL,NULL,NULL,'5','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','0.34',NULL,'4',NULL,NULL,NULL,NULL,'6','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','134.58',NULL,'4',NULL,NULL,NULL,NULL,'7','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','0.00',NULL,'4',NULL,NULL,NULL,NULL,'8','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','0.07',NULL,'4',NULL,NULL,NULL,NULL,'9','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','116.45',NULL,'4',NULL,NULL,NULL,NULL,'10','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','22838.00',NULL,'4',NULL,NULL,NULL,NULL,'11','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','0.00',NULL,'4',NULL,NULL,NULL,NULL,'12','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','0.00',NULL,'4',NULL,NULL,NULL,NULL,'13','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO',NULL,NULL,'4',NULL,NULL,NULL,NULL,'15','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO',NULL,NULL,'4',NULL,NULL,NULL,NULL,'16','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','0.00',NULL,'4',NULL,NULL,NULL,NULL,'18','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','0.00',NULL,'4',NULL,NULL,NULL,NULL,'19','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','0.00',NULL,'4',NULL,NULL,NULL,NULL,'20','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','0.00',NULL,'4',NULL,NULL,NULL,NULL,'21','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','0.00',NULL,'4',NULL,NULL,NULL,NULL,'22','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','0.00',NULL,'4',NULL,NULL,NULL,NULL,'23','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','0.00',NULL,'4',NULL,NULL,NULL,NULL,'24','6','12','2628','28')
INSERT MTS_DATA_PRELOAD([temp_metric_id],[metric_id],[year],[term_name],[temp_institution_short_name],[institution_short_name],[value],[unit_id],[currency_id],[source],[section],[page],[data_comment],[value_number],[risk_department_id],[risk_area_id],[period_id],[institution_id]) VALUES('701','701','2009','December 7','ABC CO','ABC CO','0.00',NULL,'4',NULL,NULL,NULL,NULL,'25','6','12','2628','28')

You'll see here that rows w/value_number 14 and 17 are missing - and I need to get those

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 12:33:05

are you joining on all the relevant fields?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources