Merging 3 tables and fetch data without duplication?


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
11 views
Hello!
Guys, actually this is my question.

There are 3 tables.
cities - the cities
kladr_id - unique key of the city

manager_cities - the town which are assigned by Manager
manager_id - the ID of the user from the table managers
kladr_id - from the cities table

managers - table managers
manager_id - the unique key of the Manager

The point is that one Manager can operate in several cities and I need to get on the page with the list of all the managers, their data. It turns out the table for display shall be:

Name | phone Number | City (ies)

Please help to write a query to select the name, phone number and all the cities assigned to the Manager. Simple JOINS you are not suitable, because I get duplicate data Manager, if it is enshrined in several cities.
by | 11 views

2 Answers

0 like 0 dislike
SELECT `m`.`name`, `m`.`phone`, IFNULL(`t`.`cities`, ") FROM ( SELECT `mc`.`manager_id` AS `manager_id`, GROUP_CONCAT(`c`.`name`) AS `cities` FROM `manager_cities` AS `mc` JOIN `cities` AS `c` ON `c`.`kladr_id` = `mc`.`kladr_id` GROUP BY `mc`.`manager_id` ) AS `t` RIGHT JOIN `managers` AS `m` ON `m`.`manager_id` = `t`.`manager_id`
by
0 like 0 dislike
Yes, just JOIN will not help here, you need to group the results, and keep the city concatenate:
SELECT managers.Name, managers.Phonenumber, GROUP_CONCAT(cities.City) as City(a) FROM managers JOIN manager_cities USING(manager_id) JOIN cities USING(kladr_id) GROUP BY managers.manager_id
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
asked Apr 12, 2019 by WhatIsHTML
0 like 0 dislike
3 answers
110,608 questions
257,186 answers
0 comments
35,406 users