我正在做一个应用程序,我做了一个查询,我得到一个"房间"内的用户的位置,其中有一个特定的工具激活的用户不显示在结果中。但是我想让我的用户出现在结果中,即使我已经激活了工具
SELECT users.id, users.username, users.profile_pic, room_players.points
FROM tools_by_user_by_room, users, room_players
WHERE room_players.room = 0 AND room_players.user = users.id AND tools_by_user_by_room.user = users.id AND tools_by_user_by_room.room = room_players.room AND tools_by_user_by_room.tool = 2 AND tools_by_user_by_room.active != 1
ORDER BY points DESC;
好,假设房间有id = 0
,我的用户id是1,工具有id = 2
。我要做什么才能出现在结果中,即使我有工具(id = 2
)激活(active = 1
)?
编辑:添加结构化查询(使用ANSI-92)根据我的回答下面的评论的要求:
SELECT
users.id,
users.username,
users.profile_pic,
room_players.points
FROM
room_players
join users
on room_players.user = users.id
join tools_by_user_by_room
on tools_by_user_by_room.user = users.id
and tools_by_user_by_room.room = room_players.room
WHERE
room_players.room = 0
AND tools_by_user_by_room.tool = 2
AND tools_by_user_by_room.active != 1
ORDER BY
points DESC;
我的问题的答案是:
`SELECT
users.id,
users.username,
users.profile_pic,
room_players.points
FROM
room_players
join users
on room_players.user = users.id
join tools_by_user_by_room
on tools_by_user_by_room.user = users.id
and tools_by_user_by_room.room = room_players.room
WHERE
(room_players.room = 0
AND tools_by_user_by_room.tool = 2
AND tools_by_user_by_room.active != 1)
OR (room_players.room = 0
AND tools_by_user_by_room.tool = 2
AND tools_by_user_by_room.active = 1
AND users.id = 1)
ORDER BY
points DESC;`
我已经重新格式化了代码,使它(至少对我来说)更容易理解你的代码。这是您的原始查询:
SELECT
users.id,
users.username,
users.profile_pic,
room_players.points
FROM
room_players
join users
on room_players.user = users.id
join tools_by_user_by_room
on tools_by_user_by_room.user = users.id
and tools_by_user_by_room.room = room_players.room
WHERE
room_players.room = 0
AND tools_by_user_by_room.tool = 2
AND tools_by_user_by_room.active != 1
ORDER BY
points DESC;
你现在不能仅仅通过改变where条件就得到你想要的结果吗?
SELECT
users.id,
users.username,
users.profile_pic,
room_players.points
FROM
room_players
join users
on room_players.user = users.id
join tools_by_user_by_room
on tools_by_user_by_room.user = users.id
and tools_by_user_by_room.room = room_players.room
WHERE
room_players.room = 0
AND tools_by_user_by_room.tool = 2
AND tools_by_user_by_room.active = 1
and user.id=1
ORDER BY
points DESC;