Atul Atul - 5 months ago 10
MySQL Question

MySQL: Boolean query returning true if user has not set dnd or if user has set dnd given ID must be a friend

I've these simple tables:

1. USER_DND
╔════════╦══════╗
║ UserID ║ DnD ║
╠════════╬══════╣
╚════════╩══════╝

2. USER_FRIEND
╔════════╦══════════╗
║ UserID ║ FriendID ║
╠════════╬══════════╣
╚════════╩══════════╝


USER_DND
: It contains IDs of users and their do not disturb (Dnd) flag.
UserID
is integer and is primary key.
DnD
is boolean.

USER_FRIEND
: It contains IDs of friends of users.
UserID
and
FriendID
both are integer.

Problem:

For given
UserID
and
FriendID
I want to write a single query returning boolean (
SELECT CASE WHEN EXISTS
) that returns true in either of case:

If given UserID has
DnD
set false in
USER_DND


OR

If given UserID has
DnD
set true in
USER_DND
and given FriendID exists in
USER_FRIEND
for that
UserID


Additional:

These two are tables with large number of rows. Query need to be really fast. So I don't prefer using
OR
in where clause of query
or query having subqueries. Both has severe performance penalties.

Answer

You can try using LEFT JOIN :

SELECT MAX(CASE WHEN ud.dnd = 'FALSE' THEN 'TRUE'
                WHEN uf.FriendID = YourGivenFriendID THEN 'TRUE'
                ELSE 'FALSE'
           END) as user_ind
FROM USER_DND ud
LEFT JOIN USER_FRIEND uf
 ON(ud.userID = uf.userID)
WHERE uf.userID = YourGivenID

I assumed each user can have multiple friends, therefore the MAX() , if its 1-1 , you can remove the MAX()

Comments