Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 13th, 2006, 04:51 PM
Registered User
 
Join Date: Dec 2006
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Order by based on two columns, each in two tables?

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     fake@fake.com      1
2  Wedge        8     wedge@fake.com     3
3  Bear         2     bear@fake.com      2
4  Someone      47    someone@fake.com   7
5  John         5     john@fake.com      3
6  Jane         7     jane@fake.com      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     john@fake.com      3
6  Jane         7     jane@fake.com      3
2  Wedge        8     wedge@fake.com     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     wedge@fake.com     3
6  Jane         7     jane@fake.com      3
5  John         5     john@fake.com      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
 
Old December 13th, 2006, 09:12 PM
Friend of Wrox
Points: 2,376, Level: 20
Points: 2,376, Level: 20 Points: 2,376, Level: 20 Points: 2,376, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , Australia.
Posts: 596
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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.
======================================
 
Old December 13th, 2006, 09:50 PM
Registered User
 
Join Date: Dec 2006
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

 
Old December 13th, 2006, 11:59 PM
Friend of Wrox
 
Join Date: Oct 2003
Location: colombo, WP, Sri Lanka.
Posts: 479
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via MSN to surendran Send a message via Yahoo to surendran
Default

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
 
Old December 14th, 2006, 02:42 AM
Registered User
 
Join Date: Dec 2006
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?





Similar Threads
Thread Thread Starter Forum Replies Last Post
copy columns based on cell values EricB123 Excel VBA 0 December 18th, 2007 12:51 AM
ORDER BY Date Columns.... seananderson Access 8 March 16th, 2007 05:35 AM
Order by based on two columns, each in two tables? LockesRabb PHP Databases 2 December 17th, 2006 12:19 PM
Resetting DIVs based on stacking order keithc BOOK: Beginning CSS: Cascading Style Sheets for Web Design ISBN: 978-0-7645-7642-3 1 March 6th, 2006 03:32 PM
i don't get values from 4 tables in right order Haroldd SQL Language 3 September 19th, 2003 06:49 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.