Myujth

Having trouble with doing a self-join on a homework assignment. The self-join is also pulling from a junction table rec_artist. Here is the code I'm running, and returning an empty set. Why do I return an empty set?

SELECT 
 a1.name, 
 a2.name, 
 ra.rec_id
FROM 
 artists a1, artists a2, rec_artist ra1, rec_artist ra2, rec_artist ra
WHERE 
 ra1.rec_id = ra2.rec_id
AND
 ra1.artist_id = a1.id
AND
 ra2.artist_id = a2.id
AND
 ra1.artist_id > ra2.artist_id;

Table Schema:

recordings
rec_title (varchar)
rec_id (Primary Key)
sales (dec)
genre_id (Foreign Key)

genres
id (primary key)
name (varchar)

artists
id (primary key)
name (varchar)

rec_artist (junction table)
artist_id (primary key) 
rec_id (primary key)

The question is:

List pairs of artists that have at least one recording in common.

 Your result set must have 3 columns: both artists' names and the recording_id they have in
common (use only one SQL statement).

 Only unique pairs should be displayed.

 Do not pair an artist with himself/herself.

This query should not return an empty result. It should actually return a huge result. You're correctly finding the pairs of artists that have recordings in common, but each pair will then be duplicated with every rec_id in the rec_artists table, because you have no conditions on the rec_artists ra table in the WHERE clause.

You don't need a third join with rec_artists. You can simply select the rec_id column from either of the rec_artists tables in the self-join; it doesn't matter which you use, since ra1.rec_id = ra2.rec_id.

The correct query is:

SELECT a1.name, a2.name, ra1.rec_id
FROM artists a1, artists a2, rec_artist ra1, rec_artist ra2
WHERE ra1.rec_id = ra2.rec_id
AND ra1.artist_id = a1.id
AND ra2.artist_id = a2.id
AND ra1.artist_id > ra2.artist_id;

Or using ANSI JOIN syntax:

SELECT a1.name, a2.name, ra1.rec_id
FROM rec_artist AS ra1
JOIN rec_artist AS ra2 ON ra1.rec_id = ra2.rec_id AND ra1.artist_id > ra2.artist_id
JOIN artists AS a1 ON ra1.artist_id = a1.id
JOIN artists AS a2 ON ra2.artist_id = a2.id

DEMO