Query question

[netfrugal]

I have 2 tables: customers, transactions

This is a one to many relationship, with 'customer_id' as the matching field.

A customer will have approved transactions, and declined transactions. I want to search the transactions table for only those customers who have no existing approved transactions.

Here's what I have so far:

SELECT c.*
FROM customers c, transactions t
WHERE c.customer_id = t.customer_id
AND t.approval NOT LIKE 1

t.approval will either be 1 or 0. 0 means declined, while 1 means a successful sale.

The query I wrote still shows customers who have both successful and declines in one account.

Any help will be much appreciated!

[NogDog]

I think you could do something like:

SELECT * FROM customers WHERE customer_id NOT IN
(SELECT customer_id FROM transactions WHERE approval = 1)

[Piranha]

You can do it like this:

SELECT c.*
FROM customers c, transactions t
WHERE c.customer_id = t.customer_id
AND t.approval != 1 

But then you won't get customers without any transactions. To get those as well you should do the following:

SELECT c.*
FROM customers c
left join  transactions t on c.customer_id = t.customer_id
AND t.approval !=1