JOIN AND ORDER BY

[Clinton]

Currently I have a query set to do a search based upon no TimeOut and for a particular location using the $tablename table. That looks like:

 

$query = "SELECT * FROM $tablename WHERE TimeOut = '0000-00-00 00:00:00' AND Location = '$location'";

 

This ^ works just fine.

 

Now, one of the columns in $tablename is Position. In a different table ($tabletwo) I have a table that lists the same positions and an associated rank (a Manager would be 1, a Supervisor 2, Worker 3, etc.).

 

So in both tables, the common denominator is the Position/Positions column.

 

I would like to order the results based upon the column Position(s) using the rank established in $tabletwo (so individuals identified as Manager would be shown near the top instead of scattered throughout).

 

I can't seem to figure this out. I tried:

 

SELECT * FROM $tablename WHERE TimeOut = '0000-00-00 00:00:00' AND Location = '$location' as t1 LEFT JOIN $tabletwo AS t2 on t1.Position = t2.Positions ORDER BY t2.Rank

 

amongst about a hundred other things and can't seem to get it to work.

 

Any help would be appreciated.

 
 

[DaveyK]

Could you add the table structure(s) you are using?

[Clinton]

$tablename = Eventid, IndividualID, FirstName, LastName, Organization, Division, TimeIn, TimeOut, Location, Position, Notes

 

$tabletwo = id, Positions, Rank

[DaveyK]

where table two only holds the info as to what rank what position has...

 

 

SELECT * 
FROM $tablename  as t1
LEFT JOIN $tabletwo AS t2 
on t1.Position = t2.Positions
WHERE t1.TimeOut = '0000-00-00 00:00:00' 
AND t1.Location = '$location'
ORDER BY t2.Rank DESC
 

and this doesnt work?

Edited March 18, 2013 by DaveyK

[Clinton]

So, it worked. Looks like I was on the right track, I just had the order of operations jacked up? (Kind of like Match Class... grrrrr).