CODEWITHSUNDEEP
phpmysqlsql-order-by
Steven Dropper
Steven Dropper

Reputation: 467

MySQL ORDER BY different table column but

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

Answers (2)

Rajeesh
Rajeesh

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

Gordon Linoff
Gordon Linoff

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

Related Questions