Help with excluding records in a query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with excluding records in a query

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-10-06 : 14:29:16

I was hoping someone could help me out with this query....

I want to show companies that have a valid alternate address (joined from another table (alt_address table)).

Within the alt_address table, there are records which include null values for address_1, address_2, address_3, city, state, zip, and country. I do not want these records in my result.

I am close, just not quite there.....

select aa1.company_id, c.company_name
FROM alt_address aa1
inner join company c on c.company_id = aa1.company_id
WHERE not exists (select * from alt_address
where company_id is not null and physical_zip is null and physical_country is null and physical_state is null
and physical_address_1 is null and physical_address_2 is null and physical_address_3 is null and physical_city is null)

I am expecting 500 or so records to be returned from this query, unfortunately, none come back when run.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-06 : 14:36:53

do you mean something like this?

select aa1.company_id, c.company_name
FROM alt_address aa1
inner join company c on c.company_id = aa1.company_id
WHERE
aa1.company_id is not null and
aa1.physical_zip is not null and
aa1.physical_country is not null and
aa1.physical_state is not null and
aa1.physical_address_1 is not null and
aa1.physical_address_2 is not null and
aa1.physical_address_3 is not null and
aa1.physical_city is not null

Greetings
Webfred

Planning replaces chance by mistake

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-10-06 : 14:43:21

Yes, I went the easier route originally but I am expecting about 500 records to come back.
When I run the select statememnt within my exists statement, 10 records are found.
I then expect to run the whole query and 500 minus the 10 will be returned.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-06 : 14:43:25

I assume if all the alt-address parts are null or blank then you want to exclude

select aa1.company_id, c.company_name
FROM alt_address aa1
inner join company c on c.company_id = aa1.company_id
where isnull(physical_zip,'')+isnull(physical_country  ,'')+isnull(physical_state ,'')+isnull(physical_address_1  ,'')+
isnull(physical_address_2  ,'')+isnull(physical_address_3  ,'')+isnull(physical_city ,'') <> ''

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-06 : 14:46:57

Your not exists should look like

select aa1.company_id, c.company_name
FROM alt_address aa1
inner join company c on c.company_id = aa1.company_id
WHERE not exists (select 1 from alt_address
where company_id = aa1.company_id and physical_zip is null and physical_country is null and physical_state is null
and physical_address_1 is null and physical_address_2 is null and physical_address_3 is null and physical_city is null)

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-10-06 : 14:47:16

Hi Hanbingl, you got it.... That worked perfectly!

THANKS!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources