Post reply

Condition in JOIN clause

  • August 7, 2008 at 7:15 am

    #211030

    Hi all,

    I was wondering if its possible to do a join based on certain condition. For example:

    SELECT key, name FROM table

    JOIN table2 on table.key = table2_key

    But i would only like to have this join condition if the "key" is not null, if its null i would like to not use the join and just have "SELECT key, name FROM table"

    Is there a way of acheiving this?

    any help will be much appreciated....

    thanks

  • August 7, 2008 at 7:18 am

    #854427

    You could try "LEFT JOIN"

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • August 7, 2008 at 7:24 am

    #854431

    thanks for the reply, however this wont achieve my goal, becuase if the "key" is not null i want a inner join on the tables if not jus the select statement

  • August 7, 2008 at 7:37 am

    #854443

    YIP I think a left join will work here

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • August 7, 2008 at 7:56 am

    #854461

    You'd get a better answer if you posted your table ddl (create statements), sample data (in the form of inset statements that could be cut, pasted, and run in SSMS to load your tables), expected results based on the sample data, and what code you have currently tried that doesn't seem to be giving you the results you are trying to return.

    😎

  • August 7, 2008 at 8:17 am

    #854477

    VB (8/7/2008)

    thanks for the reply, however this wont achieve my goal, becuase if the "key" is not null i want a inner join on the tables if not jus the select statement

    As Andras and Christopher have already stated, your goal as described would be achieved by using a left join. Have you tested it?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • August 7, 2008 at 8:24 am

    #854482

    Chris,,,

    You making me nervious...

    are you following me around today :w00t:

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • August 7, 2008 at 8:25 am

    #854483

    hi,

    I have jus tested this and it has acheived wat i want, i shud of tested it b4 replying back, apologies,

    thanks for the replies

  • August 7, 2008 at 8:27 am

    #854487

    Christopher Stobbs (8/7/2008)

    Chris,,,

    You making me nervious...

    are you following me around today :w00t:

    Be afraid...be very afraid...

    Nah Chris I'm picking the easy ones, same as you 😛 😀

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • August 7, 2008 at 8:41 am

    #854494

    I think the consensus is that a left outer join will solve this problem, here is an example - is it of any use? Remember there is a circle of hell reserved for anonymous posters who make posts and then don't tell anyone when their problem is solved.

    declare @Table1 table (Table1Key int)

    declare @Table2 table (Table2Key int, Table1Key int null)

    insert into @Table1 (Table1Key) values (1)

    insert into @Table1 (Table1Key) values (2)

    insert into @Table1 (Table1Key) values (3)

    insert into @Table1 (Table1Key) values (4)

    insert into @Table2 (Table2Key, Table1Key) values (111,1)

    insert into @Table2 (Table2Key, Table1Key) values (2,2)

    select * from @Table1

    select * from @Table2

    select * from @Table1 one left outer join @Table2 two on one.Table1Key = two.Table1Key

    Results

    Table1Key

    -----------

    1

    2

    3

    4

    (4 row(s) affected)

    Table2Key Table1Key

    ----------- -----------

    111 1

    2 2

    (2 row(s) affected)

    Table1Key Table2Key Table1Key

    ----------- ----------- -----------

    1 111 1

    2 2 2

    3 NULL NULL

    4 NULL NULL

    (4 row(s) affected)

  • August 7, 2008 at 8:44 am

    #854498

    There is also a circle of hell reserved for people who are composing posts that are made obsolete by people trying to avoid previously mentioned circles.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply