By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

How to use WHERE IN syntax conditionally

P: 9
Hi,
I've got a dynamic table called @v_filter_table that is populated with some rows, ala:

Expand|Select|Wrap|Line Numbers
  1. row_id   location_id
  2.    1            102
  3.    2            102
  4.    3            104
  5.  
Now, I have a query that executes, and one of the filter criteria in the query filters on the contents of this table - ala:

Expand|Select|Wrap|Line Numbers
  1. SELECT {...} FROM {...} WHERE mytable.row_id IN (SELECT row_id FROM @v_filter_table WHERE location_id = 102)
  2.  
The problem is that I want the query to use it's own filter criteria if it can't match any rows in @v_filter_table with the location_id of 102 - since by making the filter criterion equal itself - mytable.row_id = mytable.row_id - it effectively removes itself from the WHERE clause - which is exactly what I want. E.g.

Expand|Select|Wrap|Line Numbers
  1. SELECT {...} FROM {...} WHERE mytable.row_id IN (COALESCE((SELECT row_id FROM @v_filter_table WHERE location_id = 102),mytable.row_id)) 
  2.  
The problem is, the above doesn't work if there is more then one matching row in the @v_filter_table. E.g. If I removed one of the rows with a location_id of 102, it'll work - but otherwise it won't. The return of the COALESCE statement evidently cannot be more then one row.

How would I be able to refactor this query to acheive the result I need? Any help greatly appericated!
Apr 21 '08 #1
Share this Question
Share on Google+
2 Replies


ganeshkumar08
P: 31
Hello,

How the COALESCE works??
i=COALESCE(id,myid)
i=id, if id has any value
i=myid, if id is null
so, based on this you try....

I given one example below try this...


declare @t1 Table (id int, id1 int)
insert into @t1 (id,id1)
select 1,2 union all
select 2,2 union all
select 3,3 union all
select 4,4

declare @t2 Table (myid int, id int,id2 int)
insert into @t2 (myid,id,id2)
select 1,1,1 union all
select 2,2,2 union all
select 3,3,3 union all
select 4,5,6

declare @i int
SELECT * from @t2 t1
WHERE t1.id
= COALESCE(@i ,t1.id)


Ganesh
Apr 23 '08 #2

ck9663
Expert 2.5K+
P: 2,878
Hi,
I've got a dynamic table called @v_filter_table that is populated with some rows, ala:

Expand|Select|Wrap|Line Numbers
  1. row_id   location_id
  2.    1            102
  3.    2            102
  4.    3            104
  5.  
Now, I have a query that executes, and one of the filter criteria in the query filters on the contents of this table - ala:

Expand|Select|Wrap|Line Numbers
  1. SELECT {...} FROM {...} WHERE mytable.row_id IN (SELECT row_id FROM @v_filter_table WHERE location_id = 102)
  2.  
The problem is that I want the query to use it's own filter criteria if it can't match any rows in @v_filter_table with the location_id of 102 - since by making the filter criterion equal itself - mytable.row_id = mytable.row_id - it effectively removes itself from the WHERE clause - which is exactly what I want. E.g.

Expand|Select|Wrap|Line Numbers
  1. SELECT {...} FROM {...} WHERE mytable.row_id IN (COALESCE((SELECT row_id FROM @v_filter_table WHERE location_id = 102),mytable.row_id)) 
  2.  
The problem is, the above doesn't work if there is more then one matching row in the @v_filter_table. E.g. If I removed one of the rows with a location_id of 102, it'll work - but otherwise it won't. The return of the COALESCE statement evidently cannot be more then one row.

How would I be able to refactor this query to acheive the result I need? Any help greatly appericated!
So basically, whether row_id in on on the @vfilter_table.location_id or not, you want to return the record from yourtable?

If yes, that sounds like a LEFT JOIN.

-- CK
Apr 23 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.