Joining data from 3 separate MySQL tables-CodePudding

I have 3 MySQL tables: person, review & team.

I have been able to join 2 (person & review) together, however I'd like to include data from the 3rd in my result.

Can someone explain how this is done? :-)

CREATE TABLE `person` (
  `id` int NOT NULL AUTO_INCREMENT,
  `reference` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `person` (`id`, `reference`, `email`) VALUES
(1, 'PK001',    '[email protected]');

CREATE TABLE `review` (
  `id` int NOT NULL AUTO_INCREMENT,
  `review_type` varchar(255) NOT NULL,
  `review_body` varchar(255) NOT NULL,
  `person_id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `review` (`id`, `review_type`, `review_body`, `person_id`) VALUES
(1, 'Personality',  'He has a great personality!',  1),
(2, 'Skills',   'He has multiple skills!',  1);

CREATE TABLE `team` (
  `id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `team_name` varchar(255) NOT NULL,
  `value` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `team` (`id`, `person_id`, `team_name`, `value`) VALUES
(1, 1, 'Man Utd', 500),
(2, 1, 'Real Madrid', 1500),
(3, 1, 'Ajax', 1000);

Using the following SQL:

SELECT p.id, group_concat(r.review_body)
FROM person p
inner join review r on r.person_id = p.id
group by p.id

gives me the output: He has a great personality!,He has multiple skills!

However I'd ultimately like my output to be:

He has multiple skills!,He has a great personality,Man Utd-500|Real Madrid-1500|Ajax-1000

Is this possible to do with MySQL ? Any guidance would be greatly appreciated.

I realise I could optimise things a lot better - but I just want to see if I can connect all 3 tables together and go from there.

CodePudding user response：

To get your required concatenated output you need to modify your join query.

For that your new query looks like this:

SELECT p.id, 
       GROUP_CONCAT(r.review_body) AS reviews, 
       (SELECT GROUP_CONCAT(CONCAT(team_name, '-', value) SEPARATOR '|')
        FROM team 
        WHERE team.person_id = p.id) AS teams
FROM person p
INNER JOIN review r ON r.person_id = p.id
GROUP BY p.id;

Result :