And Or Logic - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

And Or Logic

Author

Topic

tjbarr
Starting Member

8 Posts

Posted - 2009-08-18 : 15:04:09

Having trouble with a "simple" sql
Select Distinct B.AddrID

from Table_Address

Where AddrType in ('a','b','c')
and addrlocatr <> 'g'

Will give me records ID 01 and 04 below

I want a list of all id's that have no addrstatus ='g' regardless of the addrtype. So ID 01, 02 or 03 should not show up in my query but ID 04 Should.

Table_Address
Id addrtype addrstatus
01 home g
01 bus l
02 home g
03 bus g
04 bus l

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-18 : 15:06:49

The shown query is not fitting to the shown sample column names.
edit: and also the data is not fitting.
Your select would return nothing.

No, you're never too old to Yak'n'Roll if you're too young to die.

tjbarr
Starting Member

8 Posts

Posted - 2009-08-18 : 15:10:51

Posted - 08/18/2009 : 15:04:09 Show Profile Reply with Quote
Having trouble with a "simple" sql
Select Distinct AddrID

from Table_Address

Where AddrType in ('a','b','c')
and addrlocatr <> 'g'

Will give me records ID 01 and 04 below

I want a list of all id's that have no addrlocatr ='g' regardless of the addrtype. So ID 01, 02 or 03 should not show up in my query but ID 04 Should.

Table_Address
Id addrtype addrlocatr
01 a g
01 b l
02 a g
03 b g
04 b l

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-18 : 15:30:15

Here's one way:


set nocount on
declare @Table_Address table (Id int, addrtype char(1), addrlocatr char(1))
insert @table_address
select 01, 'a', 'g' union all
select 01, 'b', 'l' union all
select 02, 'a', 'g' union all
select 03, 'b', 'g' union all
select 04, 'b', 'l'

select ta.id
from   @table_address ta
Left outer join (
       select id
       from   @table_address
       where  addrLocatr = 'g'
       group by id
       ) d 
       on d.id = ta.id
where d.id is null
group by ta.id

output:
id
-----------
4

EDIT:
included sample data and output
Be One with the Optimizer
TG

tjbarr
Starting Member

8 Posts

Posted - 2009-08-18 : 15:38:33

I understand the second part but the first part:

set nocount on
declare @Table_Address table (Id int, addrtype char(1), addrlocatr char(1))
insert @table_address
select 01, 'a', 'g' union all
select 01, 'b', 'l' union all
select 02, 'a', 'g' union all
select 03, 'b', 'g' union all
select 04, 'b', 'l'

What if there are 50,000 records?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-18 : 15:40:34

<sigh>

That is just some sample data in a table variable so that I could test my query. Just use the blue part but change the "@table_address" to your table name.

Be One with the Optimizer
TG

tjbarr
Starting Member

8 Posts

Posted - 2009-08-18 : 16:01:33

Yes I figured it out. Sorry to be such a "Newbie" Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-18 : 16:06:57

No worries - we've all been there

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources