[SOLVED] Join it or double query it?

[Zane]

I'm not the sharpest tool when it comes to SQL and I"m somewhat stumped here...it seems possible though I may just be dreaming....  Honestly, I just want to keep from calling mysql_query twice and having to use a "while inside a while" approach to it.

 

Anyway. I have this table

mysql> select * from boards
    -> ;
+----+-----------------+-----+--------+
| id | name            | cat | parent |
+----+-----------------+-----+--------+
|  1 | Board 1         |   1 |      0 |
|  2 | Board 2         |   1 |      0 |
|  3 | Board 3         |   1 |      0 |
|  4 | Board 4         |   2 |      0 |
|  5 | Board 5         |   2 |      0 |
|  6 | Board 3's Sub 1 |   0 |      3 |
|  7 | Board 3's Sub 2 |   0 |      3 |
+----+-----------------+-----+--------+
7 rows in set (0.00 sec)

 

Is there anyway possible..any JOIN or some sort of magic that would allow me to achieve these results instead.

 

mysql> select * from boards
    -> ;
+----+-----------------+-----+--------+
| id | name            | cat | parent |
+----+-----------------+-----+--------+
|  1 | Board 1         |   1 |      0 |
|  2 | Board 2         |   1 |      0 |
|  3 | Board 3         |   1 |      0 |
|  6 | Board 3's Sub 1 |   0 |      3 |  // |  7 | Board 3's Sub 2 |   0 |      3 |  //.... same thing for a board with a parent id of 5 or 2
|  4 | Board 4         |   2 |      0 |
|  5 | Board 5         |   2 |      0 |
+----+-----------------+-----+--------+
7 rows in set (0.00 sec)

[cags]

Call me stupid, but I don't understand  :-\ the only difference between the first and second table seems to be the order... which I assume (but haven't checked), would happen if you...

 

SELECT * FROM `boards` ORDER BY `name`

[Zane]

yeah.. well the names of the boards were to be self explanatory.

 

My goal is to have one SQL command that will display all the boards in their normal order...but putting the sub-boards beneath their parents.

 

 

[Psycho]

Just create a dynamic field for sorting purposes. Did you have any logic in mind that you wanted to use for the ordering of the sub boards? The following will order them as you asked - sub boards will be further ordered simply by their ID

 

SELECT * , IF(parent=0, id, CONCAT(parent, '.', id)) AS sort
FROM `boards` 
ORDER BY sort ASC 

 

+----+-----------------+-----+--------+------+
| id | name            | cat | parent | sort |
+----+-----------------+-----+--------+------+
|  1 | Board 1         |   1 |      0 | 1    |
|  2 | Board 2         |   1 |      0 | 2    |
|  3 | Board 3         |   1 |      0 | 3    |
|  6 | Board 3's Sub 1 |   0 |      3 | 3.6  |
|  7 | Board 3's Sub 2 |   0 |      3 | 3.7  |
|  4 | Board 4         |   2 |      0 | 4    |
|  5 | Board 5         |   2 |      0 | 5    |
+----+-----------------+-----+--------+------+

[doa24uk]

Try SQL Union

 

 

http://www.tizag.com/sqlTutorial/sqlunion.php

[Zane]

That couldn't have worked more perfectly.. Thanks Mjd!

mysql> SELECT * , IF(parent=0, id, CONCAT(parent, '.', id)) AS sort
    -> FROM `boards`
    -> ORDER BY sort ASC;
+----+------------------+-----+--------+------+
| id | name             | cat | parent | sort |
+----+------------------+-----+--------+------+
|  1 | Board 1          |   1 |      0 | 1    |
|  2 | Board 2          |   1 |      0 | 2    |
| 11 | Two's Subboard 1 |   0 |      2 | 2.11 | // I can't figure out why the 11 comes before the 9 though.
|  9 | Two's Subboard 1 |   0 |      2 | 2.9  |
|  3 | Board 3          |   1 |      0 | 3    |
|  6 | Board 3's Sub 1  |   0 |      3 | 3.6  |
|  7 | Board 3's Sub 2  |   0 |      3 | 3.7  |
|  4 | Board 4          |   2 |      0 | 4    |
|  5 | Board 5          |   2 |      0 | 5    |
| 10 | Sub For Five     |   0 |      5 | 5.10 | //The same with the 10.
|  8 | Sub For Five     |   0 |      5 | 5.8  |
+----+------------------+-----+--------+------+
11 rows in set (0.01 sec)

[Zane]

I guess I spoke to soon

 

Once I begin to add more boards they show up at the top of the list

mysql> SELECT * , IF(parent=0, id, CONCAT(parent, '.', id)) AS sort FROM `boards
` ORDER BY sort;
+----+------------------+--------+------+
| id | name             | parent | sort |
+----+------------------+--------+------+
|  1 | Board 1          |      0 | 1    |
| 15 | Board 6          |      0 | 15   | //  | 16 | Board 7          |      0 | 16   | // |  2 | Board 2          |      0 | 2    |
| 13 | Two's Subboard 2 |      2 | 2.13 |
|  9 | Two's Subboard 1 |      2 | 2.9  |
|  3 | Board 3          |      0 | 3    |
|  6 | Board 3's Sub 1  |      3 | 3.6  | //These two are the only ones acting corectly...I wonder why?
|  7 | Board 3's Sub 2  |      3 | 3.7  | //
|  4 | Board 4          |      0 | 4    |
|  5 | Board 5          |      0 | 5    |
| 12 | Sub For Five 2   |      5 | 5.12 |
|  8 | Sub For Five     |      5 | 5.8  |
+----+------------------+--------+------+
13 rows in set (0.08 sec)

 

I've tried doing this

ORDER BY sort, id;

  this

ORDER BY id, sort;

  and this

Group By id, ORDER BY sort;

but I'm clueless as to why it does it like that

 

[Psycho]

Yeah, I thought about that after I had posted. It is ordering the custom value as a string.

 

No problem, we will just take a different approach. We will create a different dynamic field which will include the parent ID for sub-boards but will contain the record ID for parents. We can use that to sort first to get all the "boards" (parent and childs) grouped together.

 

Then we will next sort by the parent ID field. Beause the parents will always have a 0, they will be at the top of each group. Lastly we will sort by the board name so the child boards are ordered in alphabetical order.

 

Give this a try (not tested):

SELECT *, IF(parent=0, id, parent) AS `board`
FROM `boards` 
ORDER BY `board` ASC, `parent` ASC, `name` ASC

[Zane]

Wow... ok

Now that's what I'm talkin about.

 

Now it's really working like a charm and I completely understand your explanation.

Notice I deleted the cat field because it was bugging me.

mysql> SELECT *, IF(parent=0, id, parent) AS `board`
    -> FROM `boards`
    -> ORDER BY `board` ASC, `parent` ASC;
+----+------------------+--------+-------+
| id | name             | parent | board |
+----+------------------+--------+-------+
|  1 | Board 1          |      0 |     1 |
|  2 | Board 2          |      0 |     2 |
|  9 | Two's Subboard 1 |      2 |     2 |
| 13 | Two's Subboard 2 |      2 |     2 |
|  3 | Board 3          |      0 |     3 |
|  6 | Board 3's Sub 1  |      3 |     3 |
|  7 | Board 3's Sub 2  |      3 |     3 |
|  4 | Board 4          |      0 |     4 |
|  5 | Board 5          |      0 |     5 |
|  8 | Sub For Five     |      5 |     5 |
| 12 | Sub For Five 2   |      5 |     5 |
| 15 | Board 6          |      0 |    15 |
| 16 | Board 7          |      0 |    16 |
+----+------------------+--------+-------+
13 rows in set (0.00 sec)

 

and I'm not so much worried about the subboards being in alphabetical order if that was your thought.  I was just trying to get them to be in ID numerical order....which they are..

 

AWSOME.