Records and no records - 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

Records and no records

Author

Topic

n2xlr8n
Starting Member

2 Posts

Posted - 2008-01-24 : 18:27:24

Please, help. I have two tables, each with a field called "Status". I'm trying to write a ColdFusion/SQL query that lists records from TableA where TableA.Status is 1, AND there are NO records in TableB where TableB.Status is 1. This is probably simple to many of you, but I have no SQL training, so I'm begging for help. Here's as much as I can do (I think)...

<CFQUERY NAME="queryname" DATASOURCE="datasource">
SELECT TableA.Status, TableB.Status
FROM TableA, TableB
WHERE TableA.AID = TableB.AID
AND TableA.Status = 1
AND ?
</CFQUERY>

Thanks!

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-24 : 18:34:00

Could you provide table schema and some sample data with expected results for what you are trying to do?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-24 : 18:55:34

[code]SELECT * FROM Table1 T1
WHERE T1.Status = 1
AND T1.id NOT IN (
SELECT T2.id FROM Table2 T2
WHERE T2.Status = 1 )[/code]

n2xlr8n
Starting Member

2 Posts

Posted - 2008-01-25 : 14:26:42

"NOT IN"...I didn't know about that one, and it works. Thanks!

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-01-25 : 14:44:11

Be very careful with NOT IN and nulls. It is much better to use a NOT EXISTS correlated sub-query.

Consider this: You need to make a shopping list to make ham and cheese sandwiches (ham, cheese, bread and mustard). In the fridge, you have ham and cheese and something else that is unknown (NULL). Make a shopping list of everything that you need that is NOT IN the fridge.

Jay
to here knows when

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-01-25 : 14:54:22

[code]
create table #fridge(item varchar(100))
create table #need(item varchar(100))

insert into #need
select 'ham' as 'item' union all
select 'cheese' union all
select 'bread' union all
select 'mustard'

insert into #fridge
select 'ham' as 'item' union all
select 'cheese' union all
select null

select
n.item as 'Shopping List'
from
#need n
where
n.item not in (
select
item
from
#fridge)

select
n.item as 'Shopping List'
from
#need n
where
not exists (
select 1
from
#fridge f
where
n.item = f.item)

drop table #fridge
drop table #need
[/code]

Jay
to here knows when

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources