May 18, 2006 at 12:14 pm
We have a table which looks like this
Tab1
Col1 Col2 Col3
A NULL 1
A Q 2
B NULL 3
FYI -- Combination of B and Q will never exist.
Now my requirement is I want to write and query where if I pass Q for Col2 I need to get following back
A Q 2
B NULL 3
But if I pass NULL for Col2 I need to get following back
A NULL 1
B NULL 3
Is there a simple way to do this.
Thanks,
JM
May 18, 2006 at 12:35 pm
select Col1, Col2, Col3
from Tab1
where
Col2 = 'Q'
or
Col1 = 'B'
select Col1, Col2, Col3
from Tab1
where
Col2 IS NULL
May 18, 2006 at 12:37 pm
No, this won't work as I will only know the value for col2.
May 18, 2006 at 12:58 pm
" I will only know the value for col2."
I don't understand this. Are you saying that there are other possible combinations of values for Col1 and Col2? If so, define and state how you want those handled.
If you "only" know the value of Col2, how do you define exactly what you want to show up in the first query?
May 18, 2006 at 1:04 pm
For COL2 = Q, I want to show all records where COL2 = Q or COL2 is null but Value in Col1 (rows with Null Col2) should not match with value in COL1 for COL2 = Q.
In other words I want to exclude first row.
May 18, 2006 at 1:17 pm
Try this:
select
Col1, Col2, Col3
from
Tab1
where
Col2
= 'Q'
or
(
Col1
not in (Select Col1 from Tab1 where Col2 = 'Q')
and
Col2 IS NULL)
I'm sure there's a way to do this with a self-join, but this is my Friday afternoon and my brain is already mush. This should get you what you want.
May 18, 2006 at 9:36 pm
Thank You it worked.
select Col1, Col2, Col3
from
Tab1
where
Col2
= 'Q'
or
(
Col1
not in (Select Col1 from Tab1 where Col2 = 'Q')
and
Col2 IS NULL)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply