I have a MySQL table with fields as below:
id name parent 1 Fruit 0 2 Meat 0 3 Orange 1 4 Beef 2
where parent field means the upper level id. For example Fruit id is 1, and Orange is one of the fruit so the parent is 1.
However I want to do an efficient MySQL query to fetch all records in the format parent->children->parent->children format. How can I do that?
The result record of the query should look like:
id name parent 1 Fruit 0 3 Orange 1 2 Meat 0 4 Beef 2
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
You need a recursive join which mysql doesn’t support. The only thing you can do is determine the maximum level of depth (i your case it 1 since you have p->c) and with this you can determine the number of joins needed :
maximum level of depth = number of self-joins:
SELECT p.id as parent_id, p.name as parent_id, c1.id as child_id, c1.name as child_name FROM my_table p LEFT JOIN my_table c1 ON c1.parent = p.id WHERE p.parent=0
For example if you max level of depth was 3 the you would need 3 self-joins:
SELECT p.id as parent_id, p.name as parent_id, c1.id as child_id_1, c1.name as child_name_1, c2.id as child_id_2, c2.name as child_name_2, c3.id as child_id_3, c3.name as child_name_3 FROM my_table p LEFT JOIN my_table c1 ON c1.parent = p.id LEFT JOIN my_table c2 ON c2.parent = c1.id LEFT JOIN my_table c3 ON c3.parent = c2.id WHERE p.parent=0
How about this?
select * from foods order by (case parent when 0 then id*1000 else parent*1000+id end), id
It’s not very nice, as it won’t work if you have more than 1000 foods with the same parent, but if you know that limit, it should do the trick.
Here is another option to get the results you are looking for. You can group the parent and children together by the parent’s id and then indicate which record IS the parent (isparent) … like so: (this example assumes that a parent record has a NULL value for parent.. instead of 0.. but it can work with 0 too)
SELECT IFNULL(parent,id) AS parentid, IF(parent IS NULL,1,0) AS isparent, name FROM my_table ORDER BY parentid ASC, <- groups/orders all parentid's together isparent DESC, <- orders the parent first id DESC <- orders children by their id next
The rest should be easy to figure out, grouping your output, etc.
You can use the below query with
SELECT ID, NAME, PARENT FROM MY_TABLE AS A, MY_TABLE AS B WHERE A.PARENT != 0 AND A.PARENT = B.ID UNION SELECT ID, NAME, PARENT FROM MY_TABLE WHERE PARENT == 0