[solved] combination of joins - 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)

[solved] combination of joins

Author

Topic

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-01 : 11:32:53

Hi,

is it possible to combine inner and full joins between 2 tables for different columns.

My own actual example is fairly complicated so I have made up this simplistic example so forgive the reason for joins not being sensible.

E.g.

Table A has columns: name,age,staff_Number, x,y,z

Table B columns: name,age,staff_Number, p,q,r

I want to join the 2 tables where name and staff_Number are the same (inner join) but the age may be missing from either table or else identical. So I want to display where age are the same or include a blank if age is missing.

So the select statement should be something like

Select A.name,A.age as A_age, B.age as B_age,A.staff_Number,A.x,A.y,A.z,B.p,B.q,B.r

from

A

--JOIN missing

B

on A.name=B.name, A.staff_number=B.staff_number,

--A.age and B.age JOIN missing

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 11:36:18

[code]SELECT a.name,
a.age as A_age,
b.age as B_age,
CASE
WHEN a.age = b.age THEN 'Identical'
ELSE 'Not identical'
END,
a.staff_Number,
a.x,
a.y,
a.z,
b.p,
b.q,
b.r
FROM TableA AS a
INNER JOIN TableA AS b ON b.name = a.name
AND b.staff_number = a.staff_number[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 11:37:46

[code]Select A.name,A.age as A_age, B.age as B_age,A.staff_Number,A.x,A.y,A.z,B.p,B.q,B.r
from tableA A
INNER JOIN tableB B
on A.name=B.name
AND A.staff_number=B.staff_number
AND (COALESCE(A.Age,-1)=COALESCE(B.Age,-1)
OR A.Age IS NULL
OR B.Age IS NULL)[/code]

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-01 : 11:48:49

many thanks - just what I needed and so fast.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources