mysql - 在 MySQL 中的一张表上使用两个联接？

Question

自从添加查询的中间部分(INNER JOIN)后，查询现在失败。我认为这可能是因为我在一个表(charts_extended)上有两种连接类型 - 我看不到另一种从 dj_love 表中获取所需数据的方法。

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 

Answer 1

试试这个:

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