Join Help - 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 2012 Forums

Transact-SQL (2012)

Join Help

Author

Topic

tooba
Posting Yak Master

224 Posts

Posted - 2014-01-03 : 21:14:12

Here is my test data..

CREATE TABLE Table1
(
ID INT,
P# INT,
LNAME VARCHAR(50),
FNAME VARCHAR(50),
DOB datetime
)

Create Table Table2
(
ID INT,
P# INT,
LNAME VARCHAR(50),
FNAME VARCHAR(50),
DOB DATETIME,
PH# VARCHAR(30),
Address varchar(50),
City varchar(50),
State varchar(50)
)

Insert into Table1
VALUES ('1','256','Smith','John','1/3/2013')
Insert into Table1
VALUES ('2',Null,'Larry','Ste','1/3/2011')
Insert into Table1
VALUES ('3','258','Mike','Loner','11/3/2000')
Insert into Table1
VALUES ('4','','Norman','Sam','11/3/2004')

Insert into Table2
VALUES ('10','256','Smith','John','1/3/2013','784555555','145 Ave','Chicago','IL')
Insert into Table2
VALUES ('11',Null,'Norman','Sam','11/3/2004','784575856','200 Drive','Los Angles','CA')

SELECT * from Table1 t
SELECT * from Table2 t

My question is:-

I want to link above table1 & Table2 through P# but if P# is Null I want to link through Fname+Lname+DOB

Here is my SQL
SELECT
t.FNAME,
t.LNAME,
t.DOB,
t.Gender,
t2.Address,
t2.City,
t2.State,
t2.P#
from Table1 t
JOIN Table2 t2 ON t.P# = t2.P#
I want to add some logic here to check if P# is null then use Fname+Lname+DOB..

Please guide me.

Thank You.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-04 : 05:11:19

[code]
SELECT
t.FNAME,
t.LNAME,
t.DOB,
t.Gender,
t2.Address,
t2.City,
t2.State,
t2.P#
from Table1 t
JOIN Table2 t2
ON (t.P# = t2.P#
OR (t.LNAME = t2.LNAME
AND t.FNAME = t2.FNAME
AND t.DOB = t2.DOB)
)
[/code]

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

tooba
Posting Yak Master

224 Posts

Posted - 2014-01-04 : 16:00:23

Awesome Visakh! Thanks You...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources