Join fields with group_concat not getting correct results

Databases
1

I am trying to get the related records of a table, at the moment my query looks like this

SELECT
play.`id`, play.`name`, play.`people`,
group_concat(people.`name` SEPARATOR ',') as peoplenames,
group_concat(people.`id` SEPARATOR ',') as peopleids
FROM
`theater_plays` as play,
`theater_people` as people

Here is the plays table

CREATE TABLE IF NOT EXISTS `theater_plays` (
	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(255) NOT NULL,
	`tagline` varchar(255) NOT NULL,
	`people` varchar(255) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

And here a few sample rows from the plays table

INSERT INTO `theater_plays` 
(`id`, `name`, `tagline`, `people`)
VALUES
(1, 'Test play 1', 'Test tag', '5,6'),
(2, 'Test play 2', 'Test tag', '6,2'),
(3, 'Test play 3', 'Test tag', '5');;

Here is the people table

CREATE TABLE IF NOT EXISTS `theater_people` (
	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(255) NOT NULL
	PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

And here a few sample rows from the people table

INSERT INTO `theater_people` 
(`id`, `name`)
VALUES
(2, 'Test2'),
(3, 'Test3'),
(4, 'Test4'),
(5, 'Test5'),
(6, 'Test6');

In theory if my query worked right I should be getting a result like this

id	name	people	peoplenames	peopleids
1	play 1	5,6	Test5,Test6	5,6
2	play 2	6,2	Test6,Test2	6,2
3	play 3	5	Test5		5

And I am getting this (considering I only have those records in the database, otherwise I would be getting every single id and name from the peoples’ table)

id	name	people	peoplenames		        peopleids
1	play 1	5,6	Test2,Test3,Test4,Test5,Test6	2,3,4,5,6

Can someone help me out on how I can achieve this?

2

GROUP_CONCAT is an aggregate function, and you forgot the GROUP BY clause :slight_smile:

also, it looks your join condition is missing, too

3

I think I have a different problem so I am unsure if this is even possible and I am looking for alternatives, if you take a look at the theater_plays.people column you will see I have a list of comma separated values, I am trying to select in one query the people referenced the problem is that I am either getting all the people in the peoples’ table or only the first person on the comma separated list if you take a look at this query

SELECT
play.`id`, play.`name`, play.`people`,
group_concat(people.`name` SEPARATOR ',') as peoplenames,
group_concat(people.`id` SEPARATOR ',') as peopleids
FROM
`theater_plays` as play,
`theater_people` as people
WHERE people.`id` IN(5,6)
AND play.`id` = 1
GROUP BY people.`id`

Works just fine and I get the expected results, gives me records 5 and 6 from the people table

But if I do this

SELECT
play.`id`, play.`name`, play.`people`,
group_concat(people.`name` SEPARATOR ',') as peoplenames,
group_concat(people.`id` SEPARATOR ',') as peopleids
FROM
`theater_plays` as play,
`theater_people` as people
WHERE people.`id` IN(play.`people`)
AND play.`id` = 1
GROUP BY people.`id`

I will only get record 5 from the people table, if the column had the values stored as 6,5 instead of 5,6 the I would get record 6 from people table so it looks like mysql is excluding the rest of the records

4

If possible you should normalize the play.people column. Get rid of it and create a new table play_people with a row for each play id - people id pair

5

Yes, that is what I ended up doing. thank you.

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.