Suppose you have two tables: Musicians musicianID (primary key, int) name (varchar) instrument (varchar) Gigs GigID (primary key, int) musicianID (foreign key from Musicians) location (varchar) How would I retrieve the musicianIDs which have not played a gig i.e. that don't have a reference in the Gigs table? Thanks!

Reputation: 1991

SQL example: retrieve musicianID with no gigs played?

Suppose you have two tables:

How would I retrieve the musicianIDs which have not played a gig i.e. that don't have a reference in the Gigs table?

Thanks!

Upvotes: 1

Reputation: 78155

For example:

SELECT *
FROM Musicians m
WHERE NOT EXISTS (
    SELECT 1
    FROM Gigs g
    WHERE g.musicianID = m.musicianID
)

Here's the (e.g.) MySQL documentation on EXISTS and NOT EXISTS.

Upvotes: 2

Reputation: 6724

This may be more efficient than the query suggested by martin because it doesn't use a subquery:

select musicianID
from musicians m
left join gigs g on g.musicianId = m.musicianID
where g.musicianId is null

Upvotes: 4

Reputation: 32258

SELECT
   M.*
FROM Musicians M
  LEFT OUTER JOIN Gigs G
      ON G.musicianId = M.musicianID
WHERE G.musicianId IS NULL

Upvotes: 2