using where clause with multiple joins

Question

using where clause with multiple joins

Matthew Cushing

SSCrazy

Points: 2898
More actions
May 4, 2012 at 9:22 am

#257513

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Could you post the DDL for the table, some sample data to populate the table, and the expected results based on the sample data.

Matthew Cushing SSCrazy Points: 2898 More actions · Answer 3

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?

Matthew Cushing SSCrazy Points: 2898 More actions · Answer 4

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.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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.