I am working on a project to catalogue laptops and as such am trying to re-use as much information as possible. A simplified version of the MySQL tables are:
Table: laptop
|----------------------------------|
| id | make | line | model |
| 1 | 1 | 1 | Late 2015 13" |
|----------------------------------|
Table: make
|----------------------------------|
| id | name | other info |
| 1 | Apple | |
|----------------------------------|
Table: line
|----------------------------------|
| id | name | other info |
| 1 | MacBook Pro | |
|----------------------------------|
Table: networking
|----------------------------------|
| id | name | other info |
| 1 | A wifi card | |
| 2 | Another card | |
| 3 | Yet another | |
|----------------------------------|
Table: laptop_networking
|----------------------------------|
| id | networking | laptop |
| 1 | 3 | 1 |
| 2 | 1 | 1 |
|----------------------------------|
So far I used the current statement to retrieve the data in PHP
$statement = $dbc->prepare("
SELECT l.id
, m.id AS makeID
, m.name AS makeName
, n.id AS lineID
, n.name AS lineName
, l.model
FROM laptop l
JOIN make m
ON l.make = m.id
JOIN line n
ON l.line = n.id
WHERE l.id = :laptop);
$statement->bindParam(':laptop', $anID, PDO::PARAM_INT);
$statement->execute();
$theLaptop = $statement0>fetch();
At present running this code with $anID = 1 returns
|---------------------------------------------------------------|
| id | makeID | makeName | lineID | lineName | Model |
| 1 | 1 | Apple | 1 | MacBook Pro | Late 2015 13" |
|---------------------------------------------------------------|
What I would like to do is append another column to the table which returns all names from Networking which have an ID equal to a row in laptop_networking where the laptop field is equal to the ID from the retrieved laptop row
Such as:
|------------------------------------------------------------------------------------------|
| id | makeID | makeName | lineID | lineName | model | networking |
| 1 | 1 | Apple | 1 | MacBook Pro | Late 2015 13" | Yet another, A wifi card |
|------------------------------------------------------------------------------------------|
Is this possible as my many attempts at different types of JOINs have not yielded the desired results.
Thank you