Question 20970 in Mysql Question posted in
The official MySQL documentation can be found here.

Concatenate MySQL result (Join?)

I have the following MySQL tables:

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);

If I run the following PHP:

$sql = "SELECT * FROM person, review WHERE person.id = review.person_id;";
$result = $con->query($sql);

while($row = $result->fetch_assoc())  {
    echo $row['review_body'];
    echo ' | ';
    echo $row['review_body'];
    echo '<br>';
}

My output is:

He has multiple skills! | He has multiple skills!
He has a great personality! | He has a great personality!

I would prefer to have it like this:

He has multiple skills! | He has a great personality!

I imagine I will have to wrangle my MySQL query but really not sure where to begin to achieve it this. I would really appreciate some guidance.

Tags:

3

Answers


  1. 0

    you are calling the two tables separately, you need to make an inner join between the two

    exemple:

    SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

    or

    see this link

    Login or Signup to reply.
  2. 0

    You can do it as follows :

    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

    Using group_concat to concat data from a group

    Demo here

    Login or Signup to reply.
  3. 0

    you should index your shared id in both tables and use foreign keys for better performance.
    this approach is based on the Group concat function.

    if you want 1 row for all results.

    SELECT 1,GROUP_CONCAT( review_body SEPARATOR ' | ') as goruped_result 
FROM person
INNER JOIN review
ON review.person_id = person.id;

    if you want 1 row per person.id

    SELECT person.id,GROUP_CONCAT( review_body SEPARATOR ' | ') as   
goruped_result 
FROM person
INNER JOIN review
ON review.person_id = person.id
group by person.id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search