user6219654 user6219654 - 10 months ago 38
MySQL Question

Select records from third table while comparing between to other tables

I got 3 tables:

, with these fields:

  • artists

  • transactions
    : TransactionID,WorkID,CustomerID

  • work

What I want to do is:

select the last name and first name of each artist whose work has not been sold by the gallery.

What I have done so far is this:

SELECT FirstName
, LastName
FROM listofartists
, work
, trans
WHERE listofartists.ArtistID = work.ArtistID
AND trans.WorkID NOT IN (
FROM work

jpw jpw
Answer Source

Your original query is almost correct, and if you want to keep the not in construct you can change the query as follows:

You don't need the trans table in the from clause and you need to change the where clause a bit as indicated below.

This will give you those artists that exists in the work table but whose works does not exists in the trans table:

SELECT FirstName, LastName
FROM listofartists
JOIN work ON listofartists.ArtistID = work.ArtistID 
WHERE work.WorkID NOT IN (SELECT workID FROM trans)

The query will exclude artists that doesn't have any works at all, but if an artist have both sold and unsold works it will be included - it's not clear if you want to exclude artists with both sold and unsold works.