MySQL query to join 3 tables while finding non-matches

Question

(I could not come up with an concise title)

Hello All

I have been a long time viewer of the site, but after running into an issue I cannot resolve I thought I would sign up to ask a question. I am not sure how to phrase this issue which made searching for help on it difficult and it could very well be that this has been answered and I simply do not know the right search criteria to find the answer. It may also be that there is not an answer for what I am trying to do. Apologies if I have not used the correct procedure to ask.

What I am trying to achieve I can easily do via scripting, and it may be that's really the only appropriate way to do this. My circumstance puts me in a situation where I am being asked if I can do this via MySQL query only.

ISSUE:

Here is the nature of the situation (for which I don't know how to easily summarize into a topic). I have 3 tables in a mySQL database:

Table: account
column: number (unique id)

Table: user
column: number (unique id)
column: account (links the user to an account)

Table: service
column: number (unique id)
column: user  (links the service to a user)
column: servdef (the type of service)

Basically an account holds multiple users and each user can hold multiple services.

I have been asked to write a query which will show all accounts (account.number) which do not contain services (service.number) of a particular type (service.servdef). I have seen posts on this site about using NOT EXISTS, but I have not been able to get it to work correctly for my situation. Is this even possible with a single SQL query? Thanks for any help!

Answer 1

The problem turned out to be that the version of MySQL was too old to support sub queries. One the server was upgraded to new hardware and a modern operating system, the issue was resolved. The other answers are technically correct, but only work in modern versions of MySQL. It was unbeknownst to me at the time of the question that the version of MySQL was a factor. I was not something I even thought to check.

Answer 2

NOT IN is generally the slowest form, but NOT EXISTS may be faster than LEFT JOIN depending on your engine and indexes. Try each one and see.

SELECT a.number
FROM account a
WHERE a.number NOT IN (
  SELECT u.account
    FROM user u
    INNER JOIN service s ON u.number = s.user
    WHERE s.servdef = 'your_particular_type'
)

Edit: Wrong reasoning for NOT IN, sorry. Although I find it slightly easier to read than NOT EXISTS it doesn't work well with nullable columns, although I don't think that applies here.

Answer 3

Use LEFT JOIN for that since some users have no records on table service. Try,

SELECT  a.*
FROM    account a
            LEFT JOIN `user` b
                on a.number = b.account
            LEFT JOIN service c
                ON b.number = c.`user`
WHERE   c.`user` IS NULL OR
        c.servdef <> 'particularService'

SQLFiddle Demo

Answer 4

Here is an adaptation of an accepted answer given in another post I saw yesterday.

@servdef is the service type not existing for the accounts you want to find.

select a.number
from account a 
  inner join user u on a.number = u.account
  inner join service s on u.number = s.user
group by a.number
having max(case when s.servdef = @servdef then 1 else 0 end) = 0

(Gordon if you read this, feel free to laugh)

Answer 5

Using NOT EXISTS:

SELECT a.number
FROM account a
WHERE NOT EXISTS (
  SELECT u.account
  FROM user u INNER JOIN service s ON (u.number = s.user)
  WHERE u.account = a.number
    AND s.servdef = 'your_particular_type'
)

DEMO (sqlfiddle).