I got 3 tables:
artists
transactions
works
artists
ArtistsID
LastName
FirstName
transactions
work
WorkID
ArtistsID
SELECT FirstName
, LastName
FROM listofartists
, work
, trans
WHERE listofartists.ArtistID = work.ArtistID
AND trans.WorkID NOT IN (
SELECT workID
FROM work
)
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.