I need to create a query to the database

Codes here !

Moderators: egami, macek, gesf

Post Reply
User avatar
Drummer
New php-forum User
New php-forum User
Posts: 11
Joined: Thu Dec 17, 2020 12:31 am

Wed Feb 10, 2021 11:19 pm

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
php-forum Fan User
php-forum Fan User
Posts: 137
Joined: Mon Jun 08, 2020 2:00 am
Contact:

Thu Feb 11, 2021 12:01 am

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;
User avatar
Drummer
New php-forum User
New php-forum User
Posts: 11
Joined: Thu Dec 17, 2020 12:31 am

Thu Feb 11, 2021 3:07 am

Thank you!
Post Reply