CODEWITHSUNDEEP
mysqlsqldatabase
brux
brux

Reputation: 3230

SQL Joining the correct username

Say I have the following tables

User
__________
id
username
email

FriendGame
__________
id
useroneid
usertwoid
status

I want to get games that the current user is part of, so I do this:

SELECT *
FROM FriendGame
WHERE useroneid=1663702020516206
OR usertwoid=1663702020516206
AND STATUS =1;

This is fine. Now I want to join the username, but only for the user that ISNT the supplied user (1663702020516206) since in FriendGame the given user exists EITHER as useroneid or usertwoid.

Upvotes: 0

Views: 20

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can pretty much translate your logic directly into an on clause:

SELECT fg.*
FROM FriendGame fg JOIN
     User u
     ON (fg.useroneid = 1663702020516206 and fg.usertwoid = u.id) or
        (fg.usertwoid = 1663702020516206 and fg.useroneid = u.id) 
WHERE 1663702020516206 in (fg.useroneid, fg.usertwoid) AND
      STATUS = 1;

Actually, the where clause is not necessary to get the right result set, but I think it makes the intention of the query clearer.

Upvotes: 2

Related Questions