MYSQL 与多个 JOIN 联合

Question

我正在尝试编写一个查询来从具有相同列的 2 个 (list1,list2) 表中获取列表。 还有其他方法可以重写这段代码吗？

(SELECT r.id as rid, s.title, u.username  
FROM list1 r 
JOIN drama s ON r.parent_id = s.id 
LEFT JOIN image i ON s.image_id = i.id 
LEFT JOIN user u ON r.user_id = u.user_id) 

UNION ALL 

(SELECT r.id as rid, s.title, u.username  
FROM list2 r 
JOIN movie s ON r.parent_id = s.id 
LEFT JOIN image i ON s.image_id = i.id 
LEFT JOIN user u ON r.user_id = u.user_id) 
ORDER BY rid LIMIT 10

Answer 1

如果你这样做有什么问题

SELECT r.id as rid, m.title AS movie Title, 
       d.title as DramaTitle, u.username  
FROM list1 r 
INNER JOIN movie m ON r.parent_id = m.id 
INNER JOIN drama d ON r.parent_id = d.id 
LEFT JOIN image i ON s.image_id = i.id 
LEFT JOIN user u ON r.user_id = u.user_id
ORDER BY r.id LIMIT 10 

Answer 2

SELECT r.id as rid, s.title, u.username  
FROM (SELECT l1.id, l1.user_id, l1.parent_id FROM list1 l1 
      UNION ALL 
      SELECT l2.id, l2.user_id, l2.parent_id FROM list2 l2) r
INNER JOIN drama s ON r.parent_id = s.id 
LEFT JOIN image i ON s.image_id = i.id 
LEFT JOIN user u ON r.user_id = u.user_id 
ORDER BY rid 
LIMIT 10