Condition in JOIN clause

Question

Post reply

Condition in JOIN clause

VB-839659

SSC Enthusiast

Points: 145
More actions
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

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

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

You could try "LEFT JOIN"

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

VB-839659 SSC Enthusiast Points: 145 More actions · Answer 2

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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.

😎

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 6

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

VB-839659 SSC Enthusiast Points: 145 More actions · Answer 7

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

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

Melville SSCommitted Points: 1985 More actions · Answer 9

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)

Melville SSCommitted Points: 1985 More actions · Answer 10

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.