How to use WHERE IN syntax conditionally

[yumbelie]

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

row_id   location_id
   1            102
   2            102
   3            104

Now, I have a query that executes, and one of the filter criteria in the query filters on the contents of this table - ala:

SELECT {...} FROM {...} WHERE mytable.row_id IN (SELECT row_id FROM @v_filter_table WHERE location_id = 102)

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.

SELECT {...} FROM {...} WHERE mytable.row_id IN (COALESCE((SELECT row_id FROM @v_filter_table WHERE location_id = 102),mytable.row_id))

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!

[ganeshkumar08]

Hello,

How the COALESCE works??
i=COALESCE(id,m yid)
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

[ck9663]

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

row_id   location_id
   1            102
   2            102
   3            104

Now, I have a query that executes, and one of the filter criteria in the query filters on the contents of this table - ala:

SELECT {...} FROM {...} WHERE mytable.row_id IN (SELECT row_id FROM @v_filter_table WHERE location_id = 102)

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.

SELECT {...} FROM {...} WHERE mytable.row_id IN (COALESCE((SELECT row_id FROM @v_filter_table WHERE location_id = 102),mytable.row_id))

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