sql query question 1

[Jenns]

To make my problem easier to understand I have used customers and transactions which I think everyone should be familiar with as opposed to my actual application.

I have a web page that displays a report of let's say Customers and purchase dates. It has multiple customers with multiple purchase dates. For example:

Customer Purchase Date
CustomerA 1/31/04
CustomerB 12/31/03
CustomerB 1/15/04
CustomerA 2/15/04
CustomerC 2/15/04
CustomerA 3/1/04

I have a table which houses the Customer, the purchase date and transaction details. I also have a table of Customers and purchase dates that are to be EXLUDED From the above report. For example, I don't want to display the 1/15/04 purchase record for CustomerB, and the 1/31/04 and 3/1/04 records for CustomerA. The User enters those records that they do not want to appear into a separate table. My problem is that I can exclude all of a certain customer's records but am having a hard time joining the two tables and returning records that are not equal.

Any ideas are appreciated.

Thanks!
Jenn

 

[markVII]

Use subquery. Something like this:

select * from customers where date not in (select date from details)

 

[ClaireHCM]

select a.* from tablea a left join tableb b on a.Customer = b.customer and a.purchasedt = b.purchasedt where b.Customer is null

 

[Golom]

I think that you need a correlated sub-query
[blue][tt]
Select * From Customers As C

Where NOT EXISTS

(Select * From Excluded As E
Where E.ExcludeDate = C.PurchaseDate
AND E.CustomerID = C.CustomerID )
[/tt][/blue]

 

[Jenns]

Golom's post is EXACTLY what I needed.


Thanks!
Jenn