Select left join

Question

my php session:

$get_user = $_SESSION['id']; //id of the user that logged in

my query is:

$query_select = "SELECT i.users_id,s.seen, CONCAT_WS(' ', i.users_fname, i.users_lname)
AS full_name
FROM tbl_request AS f
LEFT JOIN tbl_usersinfo AS i ON i.users_id = f.user_id
LEFT JOIN tbl_status AS s ON s.user_id = f.user_id 
WHERE (f.user_id = $get_user || f.another_user = $get_user) && f.status = 'accepted'
GROUP BY full_name ORDER BY seen DESC

tbl_request:

user_id    another_user   status
1               2        'accepted'
1               5        'accepted'
3               1         'waiting'
4               1         'accepted'  

tbl_usersinfo

users_id    users_fname       users_lname
1              michael            jackson
2              michael            jordan
3              bat                man
4              will               smith
5              sean               kingston

tbl_status

user_id     seen
1           'online'
2            'offline'
3            'online'
4            'online'
5            'offline'

lets assume that the session is equal to 1, this query will result to:

users_id   full_name     seen
4          will smith    online

it should be:

 users_id   full_name            seen
    2       michael jordan       offline
    4       will smith           online
    5       sean kingston        offline

I want to select all status that equals to "accepted", but the query above only displays when the session is equal to another_user. I want my query to be flexible. for example, when the session is equal to the user_id, it should choose the another_user instead because that's the id of his friend.

Answer 1

The problem is, that in your LEFT JOINs you always compare to the f.user_id, but it should be f.user_id only if your $get_user is in another_user column... and also the same in other way... I suggest you to remake complete your query...

There are many ways to do this, but maybe simplier will be to use UNION:

SELECT i.users_id,s.seen, CONCAT_WS(' ', i.users_fname, i.users_lname)
AS full_name
FROM tbl_request AS f
LEFT JOIN tbl_usersinfo AS i ON i.users_id = f.another_user
LEFT JOIN tbl_status AS s ON s.user_id = f.another_user 
WHERE f.user_id = $get_user && f.status = 'accepted'
UNION
SELECT i.users_id,s.seen, CONCAT_WS(' ', i.users_fname, i.users_lname)
AS full_name
FROM tbl_request AS f
LEFT JOIN tbl_usersinfo AS i ON i.users_id = f.user_id
LEFT JOIN tbl_status AS s ON s.user_id = f.user_id 
WHERE f.another_user = $get_user && f.status = 'accepted'
GROUP BY full_name ORDER BY seen DESC