User has many computers, computers have many attributes in different tables, best way to JOIN?
- by krismeld
I have a table for users:
USERS:
ID | NAME |
----------------
1 | JOHN |
2 | STEVE |
a table for computers:
COMPUTERS:
ID | USER_ID |
------------------
13 | 1 |
14 | 1 |
a table for processors:
PROCESSORS:
ID | NAME |
---------------------------
27 | PROCESSOR TYPE 1 |
28 | PROCESSOR TYPE 2 |
and a table for harddrives:
HARDDRIVES:
ID | NAME |
---------------------------|
35 | HARDDRIVE TYPE 25 |
36 | HARDDRIVE TYPE 90 |
Each computer can have many attributes from the different attributes tables (processors, harddrives etc), so I have intersection tables like this, to link the attributes to the computers:
COMPUTER_PROCESSORS:
C_ID | P_ID |
--------------|
13 | 27 |
13 | 28 |
14 | 27 |
COMPUTER_HARDDRIVES:
C_ID | H_ID |
--------------|
13 | 35 |
So user JOHN, with id 1 owns computer 13 and 14. Computer 13 has processor 27 and 28, and computer 13 has harddrive 35. Computer 14 has processor 27 and no harddrive.
Given a user's id, I would like to retrieve a list of that user's computers with each computers attributes.
I have figured out a query that gives me a somewhat of a result:
SELECT computers.id, processors.id AS p_id, processors.name AS p_name, harddrives.id AS h_id, harddrives.name AS h_name,
FROM computers
JOIN computer_processors ON (computer_processors.c_id = computers.id)
JOIN processors ON (processors.id = computer_processors.p_id)
JOIN computer_harddrives ON (computer_harddrives.c_id = computers.id)
JOIN harddrives ON (harddrives.id = computer_harddrives.h_id)
WHERE computers.user_id = 1
Result:
ID | P_ID | P_NAME | H_ID | H_NAME |
-----------------------------------------------------------
13 | 27 | PROCESSOR TYPE 1 | 35 | HARDDRIVE TYPE 25 |
13 | 28 | PROCESSOR TYPE 2 | 35 | HARDDRIVE TYPE 25 |
But this has several problems...
Computer 14 doesnt show up, because it has no harddrive.
Can I somehow make an OUTER JOIN to make sure that all computers show up, even if there a some attributes they don't have?
Computer 13 shows up twice, with the same harddrive listet for both. When more attributes are added to a computer (like 3 blocks of ram), the number of rows returned for that computer gets pretty big, and it makes it had to sort the result out in application code. Can I somehow make a query, that groups the two returned rows together? Or a query that returns NULL in the h_name column in the second row, so that all values returned are unique?
EDIT:
What I would like to return is something like this:
ID | P_ID | P_NAME | H_ID | H_NAME |
-----------------------------------------------------------
13 | 27 | PROCESSOR TYPE 1 | 35 | HARDDRIVE TYPE 25 |
13 | 28 | PROCESSOR TYPE 2 | 35 | NULL |
14 | 27 | PROCESSOR TYPE 1 | NULL | NULL |
Or whatever result that make it easy to turn it into an array like this
[13] =>
[P_NAME] =>
[0] => PROCESSOR TYPE 1
[1] => PROCESSOR TYPE 2
[H_NAME] =>
[0] => HARDDRIVE TYPE 25
[14] =>
[P_NAME] =>
[0] => PROCESSOR TYPE 1
