Conditional join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Conditional join

Author

Topic

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2008-02-26 : 04:08:01

Hi All,

I have a simple select statement.
I want to know if i can match a column based on some condition.
for eg.

select col1,col2,col3 from tab1 where col1 = 'something'
and col2 = 'someotherthing'.

I want to have the 2nd join only if 'someotherthing' is not null

Sounds simple but i am unaware.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-26 : 04:21:06

For join, you need to have two tables. Where is your second table?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 04:26:58

Probably this is what you're looking at:-
select col1,col2,col3 from tab1 where col1 = 'something'
and (col2 = 'someotherthing' or col2 IS NULL)

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2008-02-26 : 05:40:43

the second join is going to be a column value and exactly the JOIN.
I wish to perform the 'equation check' if and only if the value i mentioned as 'someotherthing' is not null.

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2008-02-26 : 05:40:47

the second join is going to be a column value and exactly the JOIN.
I wish to perform the 'equation check' if and only if the value i mentioned as 'someotherthing' is not null.

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2008-02-26 : 05:41:34

the second join is going to be a column value and NOT exactly the JOIN.
I wish to perform the 'equation check' if and only if the value i mentioned as 'someotherthing' is not null.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-26 : 05:50:45

Repeating same thing thrice is not going to help. Provide some sample data and table structure to support what you are saying. That will make it much better for us to understand.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2008-02-26 : 06:02:25

Sorry Gentleman,
It was some browser problem that it loaded the page thrice.
Anyways, Let me make it simple
I have a table tab with 2 columns ID and name ( Both columns being not null)
further i have an application that gives me these 2 columns as input.
Now i have do something like this

select ID, name from tab where ID = 'something' and name = 'someotherthing'

now, the other comparision "and name = 'someotherthing' " has to be conditional.
More clearly, i wish to do the comparision only if i have some value from the application.
other wise i will go ahead with only first comparision.

Hope i am pretty clear now.

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-26 : 06:04:06

[code]select ID, name from tab where ID = @ID
and (name = @name or @name is NULL)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 06:06:11

quote:
Originally posted by vishu_av

Sorry Gentleman,
It was some browser problem that it loaded the page thrice.
Anyways, Let me make it simple
I have a table tab with 2 columns ID and name ( Both columns being not null)
further i have an application that gives me these 2 columns as input.
Now i have do something like this

select ID, name from tab where ID = 'something' and name = 'someotherthing'

now, the other comparision "and name = 'someotherthing' " has to be conditional.
More clearly, i wish to do the comparision only if i have some value from the application.
other wise i will go ahead with only first comparision.

Hope i am pretty clear now.

Thanks

select id,name from tab1 where id = @id
and (name = @name or @name IS NULL)

@name & @id you pass from application

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2008-02-26 : 06:11:31

I had tried that.
The problems is if i am not passing @name in the above solution i dont get any rows.
Infact, i expect the select statement ignores the comparision if @name is empty or null to be precised.
I dont want to go for a dynamic query as i the application doesnt support T-SQL query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 06:34:52

Build the string conditionally from application then. I guess you are using inline queries.

ie.
If @name !='' then
query with name param
else
query with only id param

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-26 : 06:39:12

[code]select ID, name from tab where ID = @ID
and (name = @name or IsNull(@name,'')='')[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2008-02-26 : 22:29:26

Hi Harsh,
It still doesnt fetch me any thing if @name is empty :-(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 23:22:40

Can you post how you are buiding the query? Are you building it inline from application?

vishu_av
Yak Posting Veteran

69 Posts

Posted - 2008-02-27 : 22:52:15

I dont really know if its inline.
I will be getting 2 variables as input to the query. As mentioned earlier, assuming the 2 variables to be
@var1 and @var2.
select ID, name from tab where ID = @var1 and name = @var2
Now if i get '' value in @var2 or @var1... I should be neglecting the comparision and go ahead.

This is what i am expecting..!!!

for eg.. assuming @var2 = ''

my query has to be
select ID, name from tab where ID = @var1

and if @var1 = ''

it has to be
select ID, name from tab where name = @var2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-28 : 01:57:37

quote:
Originally posted by vishu_av

I dont really know if its inline.
I will be getting 2 variables as input to the query. As mentioned earlier, assuming the 2 variables to be
@var1 and @var2.
select ID, name from tab where ID = @var1 and name = @var2
Now if i get '' value in @var2 or @var1... I should be neglecting the comparision and go ahead.

This is what i am expecting..!!!

for eg.. assuming @var2 = ''

my query has to be
select ID, name from tab where ID = @var1

and if @var1 = ''

it has to be
select ID, name from tab where name = @var2

Exactly. I was telling you do this where you give the query for execution using if else construct

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources