Mysql – Multi Select Statement With inner join multiple table

MySQL

user table

id   u_id   name   phone       email
1    912    abcd   23424    abcd@email.com
2    913    xyz    45342     xyz@email.com
3    916    pqrt   23239    wdiw@email.com

subject table

s_id   name
    1     eng
    2     math
    3     phy

city table

c_id   name
    1     city1
    2     city2
    3     city3

location table

l_id   name
    1     loc1
    2     loc2
    3     loc3

user_to_subject table

u_id    s_id
913      2
913      3

user_to_city table

u_id    c_id
913      1
913      3

user_to_location table

u_id    l_id
913      2
913      3

What I am trying to do is to select one statement with complete details. This is what I have tried.

SELECT U.*, GROUP_CONCAT(S.`name`) AS subject, GROUP_CONCAT(C.`name`) AS class, 
GROUP_CONCAT(L.`name`) AS location 
FROM `user` U 
  INNER JOIN `user_to_subject` UTS ON U.`u_id`=UTS.`u_id` 
  INNER JOIN `subjects` S ON S.`s_id`=UTS.`s_id` 
  INNER JOIN `user_to_class` UTC ON U.`u_id`=UTC.`u_id` 
  INNER JOIN `city` C ON C.`c_id`=UTC.`c_id` 
  INNER JOIN `user_to_location` UTL ON U.`u_id`=UTL.`u_id` 
  INNER JOIN `location` L ON L.`l_id`=UTL.`l_id` 
WHERE U.`u_id`=913

I got duplicate subjects, cities and location. Not sure what I am doing wrong here.

Best Answer

Here is the answer

SELECT U.*,
       GROUP_CONCAT(DISTINCT S.`name`) AS subject,
       GROUP_CONCAT(DISTINCT C.`name`) AS class,
       GROUP_CONCAT(DISTINCT L.`name`) AS location
FROM `user` U INNER JOIN
     `user_to_subject` UTS
     ON U.`u_id` = UTS.`u_id` INNER JOIN
     `subjects` S
     ON S.`s_id` = UTS.`s_id` INNER JOIN
     `user_to_class` UTC
     ON U.`u_id` = UTC.`u_id` INNER JOIN
     `class` C
     ON C.`c_id` = UTC.`c_id` INNER JOIN
     `user_to_location` UTL
     ON U.`u_id` = UTL.`u_id` INNER JOIN
     `location` L
     ON L.`l_id`=UTL.`l_id`
WHERE U.`u_id` = 913;

Find the fiddle here. http://sqlfiddle.com/#!9/cbbbda/1

Related Question