Reputation: 467
I've been struggling a lot with this and it's really complicated so please focus because it's even hard to explain.
I have a table friends with columns:
id, friend1, friend2, since
And I want to display all user's friends ordered by the activity of that user from different table (table name: accinfo, column: lastact
) where value of lastact
is a php time.
The problem is that I don't know which column is the friend... it could either be friend1
or friend2
but that depends...
How could I find out which column is the friend's name and not the name of user? I obviously need to check it in the SQL itself to get the friends sorted out by the latest activity. Thank you.
Tables:
friends
id | friend1 | friend2 | since
1 | bob | joe | null
2 | kate | jane | null
3 | bob | robby | null
accinfo
id | username | lastact
1 | bob | 1483323711
2 | joe | 1483323701
3 | kate | 1483323642
4 | jane | 1483311256
5 | robby | 1483321234
Upvotes: 1
Views: 73
Reputation: 13
Try this,
SELECT acf.username FROM friends fds, accinfo acf WHERE (acf.username=fds.friend1 OR acf.username=fds.frien2) ORDER BY acf.lastact DESC
Upvotes: 1
Reputation: 1271241
One method is a conditional join:
select f.*, ai.lasact
from friends f join
accinfo ai
on (ai.userid = f.friend1 and friend2 = $userid) or
(ai.userid = f.friend2 and friend1 = $userid)
where $userid in (f.friend1, f.friend2)
order by lastacct desc;
Upvotes: 1