May 4, 2012 at 9:22 am
I have a query that I've written that is pulling data from joined selects. I have a need to look at the results and exclude rows that have results that all equal 'US'.
when I include a where clause, it removes rows that have US for ANY of them, not ALL.
so for example:
select a.country, b.country, c.country from
(select * from table1 where code = 1) a inner join
(select * from table1 where code = 2) b on a.seq = b.seq inner join
(select * from table1 where code = 3) c on a.seq = c.seq
where (a.country <> 'US' and b.country <> 'US' and c.country <> 'US')
I end up with a really small set of values that are eliminating any instance of where the country = US. I only want to eliminate where the country in all three is US.
Any ideas? I can't put the where in each query, because I'd end up with the same small resultset. I'm only looking to eliminate rows where all three values are US.
May 4, 2012 at 9:55 am
Matthew Cushing (5/4/2012)
I have a query that I've written that is pulling data from joined selects. I have a need to look at the results and exclude rows that have results that all equal 'US'.when I include a where clause, it removes rows that have US for ANY of them, not ALL.
so for example:
select a.country, b.country, c.country from
(select * from table1 where code = 1) a inner join
(select * from table1 where code = 2) b on a.seq = b.seq inner join
(select * from table1 where code = 3) c on a.seq = c.seq
where (a.country <> 'US' and b.country <> 'US' and c.country <> 'US')
I end up with a really small set of values that are eliminating any instance of where the country = US. I only want to eliminate where the country in all three is US.
Any ideas? I can't put the where in each query, because I'd end up with the same small resultset. I'm only looking to eliminate rows where all three values are US.
where NOT(a.country='us' and b.country='us' and c.country='us')
MVDBA
May 4, 2012 at 9:57 am
Could you post the DDL for the table, some sample data to populate the table, and the expected results based on the sample data.
May 4, 2012 at 9:57 am
awesome, thanks man.
If I had another condition where a and b = us and c = null, can I just add another statement like this after the initial where?
May 4, 2012 at 9:59 am
I would Lynn, but I just started a new job and I don't think they'd appreciate me posting stuff from our internal apps.
I hear that it'd help with diagnosing things, but I don't want to rock the boat.
May 4, 2012 at 10:04 am
Matthew Cushing (5/4/2012)
awesome, thanks man.If I had another condition where a and b = us and c = null, can I just add another statement like this after the initial where?
you should be able to - just make sure you think about what logic you need
MVDBA
May 4, 2012 at 10:05 am
Matthew Cushing (5/4/2012)
I would Lynn, but I just started a new job and I don't think they'd appreciate me posting stuff from our internal apps.I hear that it'd help with diagnosing things, but I don't want to rock the boat.
You can obfuscate the table names, column names, and sample data as long as it still represents the problem you are trying to solve.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply