Select records from third table while comparing between to other tables

Question

I got 3 tables:

artists

,

transactions

and

works

, with these fields:

artists
:
ArtistsID
,
LastName
,
FirstName

transactions
: TransactionID,WorkID,CustomerID

work
-
WorkID
,
ArtistsID

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 (

        SELECT workID

        FROM work

    )

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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.

Select records from third table while comparing between to other tables

Sign up