November 4, 2010 at 6:22 pm
Hi I got to join two tables based on some conditions
If COLA contains 'xyz' then i use one join statement or else different one
like
if (Tab1.colA='XYZ')
begin
select * from tab1 inner join tab2
on tab1.colb =tab2.colb
end
else
begin
select * from tab1 inner join tab2
on tab1.colA=tab2.colA
end
I tried but getting some wierd errors
November 4, 2010 at 6:41 pm
could you be a little more specific regarding the errors you get?
November 4, 2010 at 8:30 pm
Not having any DDL (create table statements), sample data (series of INSERT INTO statements for the tables), and expected results; is this what you are trying to accomplish?
select
*
from
tab1
inner join tab2
on (tab1.colB = tab2.colB
and tab1.colA = 'XYZ')
union
select
*
from
tab1
inner join tab2
on (tab1.colA = tab2.colA
and tab1.colA <> 'XYZ')
;
November 5, 2010 at 8:06 am
Completely untested, but something like this could work:
SELECT
*
FROM
Person.Contact AS C JOIN
HumanResources.Employee AS E
ON CASE WHEN C.FirstName LIKE 'J%' AND
C.ContactID = E.ContactID THEN 1
WHEN C.rowguid = E.rowguid THEN 1
ELSE 0
END = 1
Although I believe Lynn's solution will perform better.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 5, 2010 at 8:16 am
His error lies in the 1st statement itself; since he is trying to reference a column comparison in the IF statement.
I am assuming he wants to check if the column in the tab1 is 'XYZ' then join on the respective tables.
IF EXISTS (SELECT 1
FROM tab1
WHERE tab1.colA = 'XYZ' )
BEGIN
SELECT * FROM TAB1
INNER JOIN TAB2
ON TAB1.COLB =TAB2.COLB
END
ELSE
BEGIN
SELECT * FROM TAB1 INNER JOIN TAB2
ON TAB1.COLA=TAB2.COLA
END
This could be modified to include lynn's code as well.
November 8, 2010 at 10:52 am
Thanq so much guys. Your answers helped me out...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply