January 3, 2014 at 7:16 pm
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.
January 3, 2014 at 9:42 pm
I don't think you can make ON clauses conditional. Try replacing your inner join with 2 seperate LEFT OUTER JOINs to Table2.
Then use ISNULL() in your SELECT clause.
SELECT
t.FNAME,
t.LNAME,
t.DOB,
t.Gender,
ISNULL(t2.Address, t3.Address) AS Address,
ISNULL(t2.City, t3.City) AS City,
ISNULL(t2.State, t3.State) AS State,
t2.P#
from Table1 t
LEFT OUTER JOIN Table2 t2 ON t.P# = t2.P#
LEFT OUTER JOIN Table2 t3 ON t.FNAME = t3.FNAME AND t.LName = t3.LName AND t.DOB = t3.DOB
Does that work for you? If you ever have 3 or more conditional criteria, you can do basically the same thing, but you'd need to use COALESCE() rather than ISNULL().
January 5, 2014 at 6:50 pm
SQL and Soda (1/3/2014)
I don't think you can make ON clauses conditional.
Correction. It is possible to have conditional ON clauses using CASE. However, in this case I don't think I'd recommend it because of the fact you'd need to concatenate 3 columns and they're not all character (I'm not sure I'd recommend it even if they were all character because I believe the result would still be non-SARGable).
Just for fun, here's another approach:
SELECT t.FNAME, t.LNAME, t.DOB
,t.[Address], t.City, t.[State], t.P#
FROM
(
SELECT t.FNAME, t.LNAME, t.DOB
,t2.[Address], t2.City, t2.[State], t2.P#
FROM Table1 t
JOIN Table2 t2 ON t.P# = t2.P#
WHERE t.P# IS NOT NULL
UNION ALL
SELECT t.FNAME, t.LNAME, t.DOB
,t2.[Address], t2.City, t2.[State], t2.P#
FROM Table1 t
JOIN Table2 t2 ON t.LNAME = t2.LNAME AND t.FNAME = t2.FNAME AND t.DOB = t2.DOB
WHERE t.P# IS NULL
) t
Can't say which would perform better. You'd need to run a test to be sure. It would probably depend a lot on whether there's an INDEX on LNAME, FNAME, DOB (which I'd probably recommend).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 6, 2014 at 8:19 am
SELECT a.FNAME, a.LNAME, a.DOB
, b.[Address], b.City, b.[State], ISNULL(b.P#, a.P#) AS P#
FROM @Table1 a
INNER JOIN @Table2 b
ON (a.P# = b.P#)
OR (
((a.P# IS NULL)
OR (b.P# IS NULL))
AND (a.FNAME = b.FNAME)
AND (a.LNAME = b.LNAME)
AND (a.DOB = b.DOB)
)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply