If statement

  • 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

  • could you be a little more specific regarding the errors you get?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

    ;

  • 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.

  • 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.

  • 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