SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Join Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tooba
Posting Yak Master

172 Posts

Posted - 01/03/2014 :  21:14:12  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/04/2014 :  05:11:19  Show Profile  Reply with Quote

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)
    )


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

tooba
Posting Yak Master

172 Posts

Posted - 01/04/2014 :  16:00:23  Show Profile  Reply with Quote
Awesome Visakh! Thanks You...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000