Using two columns for JOIN? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Using two columns for JOIN?

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-01 : 14:38:26

I have two tables:

Table1
------

item type colour date
1 A Red 24/03/2007
2 J Blue 27/03/2007
3 A Black 27/03/2007

Table2
------
code id description

1 A Pending
1 J Not listed
3 J Returned

I wish to JOIN Table1.type to Table2.description when Table1.item = Table2.code AND Table1.type = Table2.id

to give the result:

item type colour date
1 Pending Red 24/03/2007
2 Blue 27/03/2007
3 Black 27/03/2007

How can I do this please?

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-02-01 : 16:00:06

It looks like you need an outer join where you want all records from Table1 and the description from Table2 if it's there. Am I understandin that correctly?

If so, try this:

SELECT T1.item, T2.description, T1.colour, T1.date
FROM Table1 T1 LEFT OUTER JOIN Table2 T2 ON T1.item = T2.code AND T1.type = T2.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources