CODEWITHSUNDEEP
mysqljoin
user3304303
user3304303

Reputation: 1043

How to properly join two tables to use alternative ORDER BY

Two tables...

  1. people (personid, name, mainordering)
  2. entries (userid, personid, altordering)

"personid" is the common field. My app displays a draggable list users can move around. When done, they click to "lock" in their order.

Table : people

+----------+---------+--------------+
| personid | name    | mainordering |
+----------+---------+--------------+
| 1        | Bob     |   2          |
| 2        | Charlie |   4          |
| 3        | Jim     |   1          |
| 4        | Doug    |   3          |
+----------+---------+--------------+

So using mainordering, it would display:

Jim
Bob
Doug
Charlie

entries table might have (for user 16):

+--------+----------+-------------+
| userid | personid | altordering |
+--------+----------+-------------+
| 16     |   1      |   3         |
| 16     |   2      |   1         |
| 16     |   3      |   2         |
| 16     |   4      |   4         |
+--------+----------+-------------+

So if user 16 has already submitted his entry BUT NOT LOCKED IT IN, I want to display his list using altordering. i.e.

Charlie
Jim
Bob
Doug

I'm struggling with the proper join to use. Here is what I tried and isn't working (it's simply ordering by mainordering still)...

$sql = "SELECT * from entries
    WHERE userid=".$_SESSION['userid']."
    LEFT JOIN people ON entries.personid = people.personid
    ORDER BY altordering";

Any thoughts would be much appreciated. Thank you...

Upvotes: 0

Views: 29

Answers (1)

Tomaso Albinoni
Tomaso Albinoni

Reputation: 1013

Are you sure you don't get an error when using WHERE before JOIN?

It should work like this:

SELECT people.*
FROM people
JOIN entries ON entries.personid = people.personid
WHERE entries.userid={$_SESSION['userid']}
ORDER BY entries.altordering

I assume entries.personid will always have a matching person in people, so you should use an INNER JOIN. You would use FROM entries LEFT JOIN people if you wanted to retrieve altordering even for non-existing people.

Upvotes: 2

Related Questions