CODEWITHSUNDEEP
phpmysqljoin
Jim Valentine
Jim Valentine

Reputation: 23

MySQL join, return 1 row from one table and multiple rows from another table as an array or list

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

Upvotes: 0

Views: 2789

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

Try this query:

SELECT laptop.id,
       make.id AS makeID,
       make.name AS makeName,
       line.id AS lineID,
       line.name AS lineName,
       laptop.model,
       t.networking
FROM laptop
INNER JOIN make
    ON laptop.make = make.id
INNER JOIN line
    ON laptop.line = line.id
INNER JOIN
(
    SELECT t1.laptop, GROUP_CONCAT(t2.name) AS networking
    FROM laptop_networking t1
    INNER JOIN networking t2
        ON t1.networking = t2.id
    GROUP BY t1.laptop
) t
    ON laptop.id = t.laptop
WHERE laptop.id = :laptop

Demo here:

Rextester

Upvotes: 1

Related Questions