I have a database that is used for recording transactions by customers.
The table (transactions) is set out as follows:
ID | Added-date | userid | amount | department | transaction-date | addedby 1 yyyy-mm-dd P1001 9.78 dpt 1 yyyy-mm-dd username 1 yyyy-mm-dd P1023 19.78 dpt 2 yyyy-mm-dd username 1 yyyy-mm-dd P1021 39.78 dpt 3 yyyy-mm-dd username 1 yyyy-mm-dd T1501 9.78 dpt 2 yyyy-mm-dd username
What I would like to do is be able to view all of the users who have never bought anything in department x .
Does anyone have any suggestions as to the best way to go about this?
There are several ways of doing this. I like the syntax of an OUTER JOIN / NULL check:
select distinct t.userid
from transactions t
left join transactions t2
on t.userid = t2.userid and t2.department = 'Some Dept'
where t2.userid is null
DISTINCT may or may not be needed -- depends on your desired results.
You could also use NOT IN (but I have seen performance issues in MySQL with this):
select distinct userid
from transactions
where userid not in (
select userid
from transactions
where department = 'Some Dept')
When you have just a few customer you can loop through all records and sort by field department in some order. Or you can group them by field department. For example a 2-dim array:
$sort[$department][]=$record;
Then you can the filtering with another programming logic.
There is also a subquery syntax:
SELECT *
FROM users
WHERE (SELECT COUNT(id) FROM transactions WHERE transactions.userid = users.id) = 0