CODEWITHSUNDEEP
mysqlsqljoin
Franco
Franco

Reputation: 2926

Using two joins on one table in MySQL?

Since adding the middle part of the query (INNER JOIN) the query is now failing. I think this is probably because I have two types on joins on one table (charts_extended) - I cannot see another way to grab the required data from the dj_love table.

SELECT DISTINCT releases . * 
FROM releases
INNER JOIN artist_love ON releases.all_artists LIKE CONCAT( '%', artist_love.artist, '%' ) 
AND artist_love.user = 'Quickinho'
INNER JOIN label_love ON releases.label_no_country = label_love.label
AND label_love.user = 'Quickinho'

INNER JOIN charts_extended ON charts_extended.release_id = releases.id
WHERE charts_extended.artist
IN (

    SELECT dj
    FROM dj_love
    WHERE user =  'Quickinho'
    )

LEFT JOIN charts_extended ON charts_extended.release_id = releases.id
AND charts_extended.artist = 'Quickinho'
WHERE charts_extended.release_id IS NULL 

ORDER BY releases.date DESC

Upvotes: 0

Views: 108

Answers (1)

Iłya Bursov
Iłya Bursov

Reputation: 24146

try this one:

SELECT DISTINCT releases . * 
FROM releases
INNER JOIN artist_love
    ON releases.all_artists LIKE CONCAT( '%', artist_love.artist, '%' ) AND artist_love.user = 'Quickinho'
INNER JOIN label_love
    ON releases.label_no_country = label_love.label AND label_love.user = 'Quickinho'
INNER JOIN charts_extended as a
    ON a.release_id = releases.id
LEFT JOIN charts_extended as b
    ON b.release_id = releases.id
        AND b.artist IN (SELECT dj FROM dj_love WHERE user =  'Quickinho')
        AND b.artist = 'Quickinho'
WHERE
    b.release_id IS NULL 

ORDER BY releases.date DESC

Upvotes: 1

Related Questions