I got 3 tables:
WHERE listofartists.ArtistID = work.ArtistID
AND trans.WorkID NOT IN (
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.