按部门过滤交易

Question

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?

Answer 1

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')