Order by based on two columns, each in two tables?

LockesRabb · December 13th, 2006, 04:51 PM

Here's a fairly complicated one-- or if it seems so to me.

Basically, I have two tables. One is called personnel. The second one is called rank.

The personnel table structure looks something like this:

id user levelofaccess email division ...etc

The data is like this:

Code:

1  LockesRabb   1     [email protected]      1
2  Wedge        8     [email protected]     3
3  Bear         2     [email protected]      2
4  Someone      47    [email protected]   7
5  John         5     [email protected]      3
6  Jane         7     [email protected]      3

The second table (rank)'s structure is like this:

id rank_name level division etc...

The data is like this:

Code:

1  Leader           1  1
2  Co-leader        1  2
3  SC Div Ldr       2  2
4  Diablo Div Ldr   2  2
5  Commander        1  3
6  Lt. General      2  3
7  General          3  3
8  Major General    4  3

As you can see, in the rank table, the division number are based on the ones in the division table (1 being the leader division, 2 being the specialist division, 3 being the starcraft division).

Here's what I'm trying to do via both PHP and SQL (simplified version):

Code:

$query = mysql_query("SELECT * FROM divisions ORDER BY id asc;");
$divisions=mysql_num_rows($query); 
while($row = mysql_fetch_array($query)) {
    $currentdivision = $row['id'];
    $divisionname = GetInfo('divisions', 'id', $currentdivision, 'name');
    $queryx = mysql_query("SELECT * FROM personnel WHERE `division`='$currentdivision' ORDER BY levelofaccess;");
    $ranks=mysql_num_rows($queryx);
    if($ranks > 0){
        $queryy == mysql_query("USING PREVQUERY ORDER BY rank.level asc;"); #Fake SQL query

Now, as you can see from the code above, especially the last line (which is fake, I was trying to show you an example of what I'd like to accomplish), I'd like to take the data from the personnel table, first order it by personnel.levelofaccess, then order it again based on the corresponding rank.level by correlating it to the id and levelofaccess columns. The reason for this complication is to enable my users to modify the rank priorities, so for example, they could change Major General to have the level of 1, and Commander to have the level of 4 and have the query be able to reorganize accurately based on the new changes.

Here's an example of before and after changes with a successful query from the starcraft division (3) when ordered by both personnel.levelofaccess and rank.level.

[BEFORE RANK CHANGES, rank table looks like this:]

Code:

1  Leader           1  1
2  Co-leader        1  2
3  SC Div Ldr       2  2
4  Diablo Div Ldr   2  2
5  Commander        1  3
6  Lt. General      2  3
7  General          3  3
8  Major General    4  3

[Which results in the query returning this:]

Code:

5  John         5     [email protected]      3
6  Jane         7     [email protected]      3
2  Wedge        8     [email protected]     3

[But if the user modified the level of priority each rank had to this:]

Code:

1  Leader           1  1
2  Co-leader        1  2
3  SC Div Ldr       2  2
4  Diablo Div Ldr   2  2
5  Commander        4  3 *<---- changed
6  Lt. General      2  3
7  General          3  3
8  Major General    1  3 *<---- changed

[The query after the modification would return this:]

Code:

2  Wedge        8     [email protected]     3
6  Jane         7     [email protected]      3
5  John         5     [email protected]      3

That'd be because now the Major General rank now has a higher level of priority than the Commander rank.

Make sense? Hope I didn't complicate it too much. Thanks in advance for any input, and if none is recieved, it's certainly understandable since this stuff might be too complicated. :-)

Regards,
Don Cullen

rodmcleay · December 13th, 2006, 09:12 PM

Im not sure I found a question in there but I think you need to look into linking the tables directly without using a nested query

Have you considered a statement like this, or have I missed the point.

Code:

SELECT * FROM personnel, rank WHERE  personnel.division = rank.division order by personnel.levelofaccess, rank.level

Obviously change the select * to suit your needs.

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

LockesRabb · December 13th, 2006, 09:50 PM

Nope, instead of achieving what I was hoping it'd achieve, all it did was generate alot of duplicates...

The question in there was, how do I have it order by levelofaccess first, THEN order it based on the priority level for each rank as outlined in the rank table?

surendran · December 13th, 2006, 11:59 PM

Try this
SELECT personnel.division FROM personnel, rank WHERE personnel.division = rank.division group by personnel.division order by personnel.levelofaccess, rank.level

surendran
(Anything is Possible)
http://ssuren.spaces.msn.com

LockesRabb · December 14th, 2006, 02:42 AM

Nope, doesn't accomplish it...

This is what the query did:

Code:

division
     1
     3
     6

I don't want to select only the division column, I want to select the entire row, so wildcard (*) needs to be used for select.

I tried:

SELECT * FROM personnel, rank WHERE personnel.division = rank.division group by personnel.division order by personnel.levelofaccess, rank.level

No such luck, it didn't even display the correct order. and i want it to only get data from a specific division...

I also tried this:

SELECT * FROM personnel, rank WHERE personnel.division=3 ORDER BY personnel.levelofaccess, rank.level asc

But that only generated alot of duplicates. I also tried:

SELECT * FROM personnel WHERE personnel.division=3 ORDER BY personnel.levelofaccess, rank.level asc

no such luck.

Is there any way to do this without binding those two tables?

For example, only order by levelofaccess, store results in a PHP array, then reorganize the php array based on the priority level order stored inside the rank table?