I need to create a query to the database

[Drummer]

Hello everyone. I'm a self learner that is very new to programming.

Three tables are given:

table `worker` (worker) with data - id (worker id), first_name (name), last_name (last name)

table `child` (child) with data - worker_id (worker id), name (child name)

table `car` (machine) with data - worker_id (worker id), model (car model)


Table structure:

Code: Select all

CREATE TABLE `worker` (
  `id` int(11) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


CREATE TABLE `car` (
  `user_id` int(11) NOT NULL,
  `model` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `child` (
  `user_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It is necessary to write one SQL query that returns: names and surnames of all employees, a list of their children separated by commas and a car brand. You need to select only those workers who have or had a car (if there was a car and then it was gone, then the model field becomes null).

[simonbrahan]

The children list is the unusual part of this query. The MySQL function GROUP_CONCAT can help you here. GROUP_CONCAT will concatentate a column's values by your query's GROUP_BY clause, giving you the result you want.

To incorporate this part into your overall result, you need to treat it as a separate "table" you can join against. This is easy enough in MySQL; you can often write an entirely new query in brackets and treat it as a table or list of results. In the query below, I've done this as part of a JOIN query and given the result the name "children" for clarity. Then the rest of our query JOINs against that "table", treating the list of children for each user as just another field. The final INNER JOIN excludes any workers who do not have an entry in the "car" table.

Code: Select all

SELECT
    worker.first_name,
    worker.last_name,
    children.child_list,
    car.model
FROM worker

LEFT JOIN (
    SELECT
        user_id,
        GROUP_CONCAT(name SEPARATOR ', ') as child_list
    FROM child
    GROUP BY user_id
) as children ON worker.id = children.user_id

INNER JOIN car ON worker.id = car.user_id;

[Drummer]

Thank you!