mysql - 将此 INNER JOIN 转换为 UNION

Question

我无法让它工作。我想将此查询转换为 UNION 以删除 notifies 的 INNER JOIN 中的 OR。

SELECT posts.status, posts.reports, posts.private_id,
    enter code hereposts.parent_id, posts.post_id,
    posts.reply_counts, posts.sender, posts.content, 
    posts.image, posts.thumb, posts.date,members.username, 
    members.avatar,members.avatar_path,members.level, members.state 
FROM posts
INNER JOIN members ON posts.sender = members.member_id 
INNER JOIN notifies ON (
    posts.post_id=notifies.post_id OR posts.parent_id=notifies.post_id
) 
WHERE  (posts.date>notifies.last_date AND posts.sender<>1) 
    AND notifies.member_id=1 
    AND posts.private_id<>0 
    AND posts.status=1 
    AND posts.reports<3  
ORDER BY posts.last_update DESC, posts.date DESC LIMIT 0,25;

我的尝试不起作用:

(SELECT posts.status, posts.reports, posts.private_id , posts.parent_id, 
    posts.post_id, posts.reply_counts, posts.sender, posts.content, 
    posts.image, posts.thumb, posts.date, members.username, members.avatar,
    members.avatar_path,members.level, members.state 
FROM posts 
INNER JOIN members ON posts.sender = members.member_id 
INNER JOIN notifies ON posts.parent_id=notifies.post_id) 

UNION

(SELECT posts.status, posts.reports, posts.private_id , posts.parent_id,  
    posts.post_id, posts.reply_counts, posts.sender, posts.content, 
    posts.image, posts.thumb, posts.date, members.username, members.avatar,
    members.avatar_path,members.level, members.state 
FROM posts 
INNER JOIN members ON posts.sender = members.member_id 
INNER JOIN notifies ON posts.post_id=notifies.post_id) 
WHERE  (posts.date>notifies.last_date AND posts.sender<>1) 
    AND notifies.member_id=1 
    AND posts.private_id<>0
    AND posts.status=1 
    AND posts.reports<3  
ORDER BY posts.last_update DESC, posts.date DESC LIMIT 0,25;

Answer 1

您似乎只将 where 子句添加到 UNION 的最后一部分，而不是同时添加到两者。

所以你从

SELECT *
FROM  A INNER JOIN
B ON (a.ID = b.ID OR a.ID = b.ID2)
WHERE tada

到

SELECT *
FROM  A INNER JOIN
B ON (a.ID = b.ID)
UNION
SELECT *
FROM  A INNER JOIN
B ON (a.ID = b.ID2)
WHERE tada

所以你需要做类似的事情

SELECT *
FROM  A INNER JOIN
B ON (a.ID = b.ID)
WHERE tada
UNION
SELECT *
FROM  A INNER JOIN
B ON (a.ID = b.ID2)
WHERE tada