CODEWITHSUNDEEP
phpmysqlsql
netrox
netrox

Reputation: 5326

How do I display children for every parent in mysql table

How do I create a query that display every parent with its children rather than to display all parents first then children. For example, right now, the mySQL table shows all COMPUTERS and then DRIVES. I want it to display for every computer, display the drives associated with that computer. I.E.: 

 COMPUTER   DRIVE        PID         ID 
 dell                    1 
 apple                   2
 apple                   3
 hp                      4
 hp                      5
            WD300FV                  1
            WD440GB                  1
            WD55                     2
            WD44X                    2...

I want it displayed like this where every computer is shown a list of its drives:

 COMPUTER   DRIVE        PID     
 dell                    1 
            WD300FV      1
            WD440GB      1
 apple                   2
            WD55         2
            WD44X        2
 apple                   3.....

Where computer has a parent ID and drive has an ID.

When I do this query SELECT * FROM table it doesn't create rows as I would like it to be displayed. I want every row that shows a computer to display a list of drives below the computer row.

Upvotes: 0

Views: 802

Answers (2)

fthiella
fthiella

Reputation: 49049

To get the rows in the order that you need, you could use this:

select
  computer,
  drive,
  coalesce(pid, id)
from
  your_table
order by
  coalesce(pid, id), computer is null, drive is not null

this will order rows by pid or (if pid is null) by id, and put rows where computer is not null at the top (since computer is null is evaluated as 0 when it's false, and 1 when it's true), and then rows with drive null at the top.

(of course, if computer and drive can't be null at the same time, you could just order by just computer is null)

You might also consider this order by clause:

order by coalesce(pid, id), computer is null, computer, drive is not null, drive

Upvotes: 0

Mike Brant
Mike Brant

Reputation: 71384

I would question your table structure. I would think computers and drives should be in two separate but related tables, since they are two different things, potentially with their own properties.

computer table:

computer_id |  manufacturer
---------------------------
1           |  dell
2           |  apple
3           |  hp

computer_id would be auto-incrementing primary key

drive table:

drive_id   |  computer_id  |  name
-----------------------------------
1          |  1            | WD300FV
2          |  1            | WD440GB
3          |  2            | WD55
4          |  2            | WD44X
5          |  3            | XYZ

drive_id would be auto-incrementing primary key

computer_id would have index (and possibly foreign key constraint if you are using InnoDB and wanted to enforce a relationship to computer table)

Then you would query like this:

SELECT c.computer_id, c.manufacturer, d.drive_id, d.name
FROM computer AS c
INNER JOIN drive AS d ON c.computer_id = d.computer_id
ORDER BY c.computer_id ASC, d.drive_id ASC

If, for whatever reason, you didn't want to go with a normalized structure like I have proposed and instead use a de-normalized structure like similar to what you already have, you should at least consider doing your de-normalization in a more sane way. There is no need for your pid logic at all if you just structured your table like this:

computer  |  drive
------------------
dell      | WD300FV
dell      | WD4440GB
apple     | WD55
apple     | WD44X
hp        | XYZ

You are just adding a level of complexity that doesn't need to be there. You could easily sort your result by "computer":

SELECT computer, drive FROM table
ORDER BY computer ASC, drive ASC

With no schema changes at all you could query like this:

SELECT computer, drive, CASE WHEN pid = '' THEN id ELSE pid END as `sort_id`
FROM table
ORDER BY `sort_id` ASC, drive ASC

Upvotes: 1

Related Questions