I'm doing a app and I made a query where I get the pontuation of users inside a "room" where the users that have a certain tool activated doesn't show in the result. But I want to have my user in the result even if I have the tool activated.
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;
Well, imagine that the room has the id = 0, my user id is 1 and the the tool has id = 2.
What I have to do to appear in the result even if I have the tool (id = 2) activated (active = 1)?
Edit: Adding structured query (using ANSI-92) as requested in comments under my answer:
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;
The answer to my question is:
`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;`
ools_by_user_by_room.active != 1. But I want the users and pontuations that don't have the tool activated, and my pontuation even if I have the tool activated. It mean that I want to add an exception to my user id.