trouble excluding records 1

[scroce]

In simplified form, i'm trying to write a SQL query that says this:

show me those records from the Customers table that have records in the BigOrders table EXCLUDING those Customer records that also have records in the SmallOrders table.

it's proving confounding. any hints would be helpful.

I'm using ADO with SQL Server

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[PHV]

A simple way:
SELECT C.*
FROM tblCustomers C INNER JOIN tblBigOrders B ON C.CustomerID = B.CustomerID
WHERE C.CustomerID NOT IN (SELECT DISTINCT CustomerID FROM tblSmallOrders)

Another way:
SELECT C.*
FROM tblCustomers C
INNER JOIN tblBigOrders B ON C.CustomerID = B.CustomerID
LEFT JOIN tblSmallOrders S ON C.CustomerID = S.CustomerID
WHERE S.CustomerID IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[fredericofonseca]

hum.

probably a
select from a
inner join b on ...
left outer join c on ...

or a
select from a
inner join b on ...
where not exist (select * from c where a.f = c.f)


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[dnoeth]

select *
from customers c
where exists 
  (select * from BigOrders
   where cust# = c.cust#
  )
and not exists
  (select * from SmallOrders
   where cust# = c.cust#
  )

Dieter

 

[scroce]

yes that looks like something i didn't think of. Let me play with that a little and i will post back

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[scroce]

donoeth

this model worked best for me - cheers to all!

select *
from customers c
where exists
(select * from BigOrders
where cust# = c.cust#
)
and not exists
(select * from SmallOrders
where cust# = c.cust#
)

I am a nobody, and nobody is perfect; therefore, I am perfect.