Prevent duplicated events returned from SQL query

I was working on a SQL query at work to find related events to given event where they have one or more similar artists.

Here’s SQL query.

SELECT
	p.ID AS id,
	p.post_title AS title,
	meta.meta_value AS startTime,
	artist_tr.term_taxonomy_id AS artistId
FROM
	wp_posts AS p
	INNER JOIN wp_postmeta AS meta ON p.ID = meta.post_id
		AND meta.meta_key = 'start_time'
	INNER JOIN wp_term_relationships AS artist_tr ON p.ID = artist_tr.object_id
	INNER JOIN wp_term_taxonomy AS artist_tax ON artist_tr.term_taxonomy_id = artist_tax.term_id
		AND artist_tax.taxonomy = 'artist'
WHERE
	p.post_type = 'event'
    -- current event id
	AND p.ID != 74
	AND p.post_status = 'active'
    -- (11, 10, 9, 8) are artists ids of current event
	AND artist_tr.term_taxonomy_id IN(11, 10, 9, 8)
ORDER BY
	meta.meta_value ASC

And this is the output of above SQL query. We have a problem as duplicated events are returned. The current query retrieves duplicate events because it checks each artist ID individually. For example, Event 1 has artist ID of 8 and 10, so it’s been found twice.

We can add a GROUP BY before ORDER BY to group by post id.

SELECT
	p.ID AS id,
	p.post_title AS title,
	meta.meta_value AS startTime,
	artist_tr.term_taxonomy_id AS artistId
FROM
	wp_posts AS p
	INNER JOIN wp_postmeta AS meta ON p.ID = meta.post_id
		AND meta.meta_key = 'start_time'
	INNER JOIN wp_term_relationships AS artist_tr ON p.ID = artist_tr.object_id
	INNER JOIN wp_term_taxonomy AS artist_tax ON artist_tr.term_taxonomy_id = artist_tax.term_id
		AND artist_tax.taxonomy = 'artist'
WHERE
	p.post_type = 'event'
    -- current event id
	AND p.ID != 74
	AND p.post_status = 'active'
    -- (11, 10, 9, 8) are artists ids of current event
	AND artist_tr.term_taxonomy_id IN(11, 10, 9, 8)
GROUP BY p.ID -- Add a group by here
ORDER BY
	meta.meta_value ASC

And this is the result coming back.

This works, but what if I want to keep those artists ids? We can add a GROUP_CONCAT.

SELECT
	p.ID AS id,
	p.post_title AS title,
	meta.meta_value AS startTime,
	-- artist_tr.term_taxonomy_id AS artistId
    GROUP_CONCAT(artist_tr.term_taxonomy_id SEPARATOR ',') AS artistIds
FROM
	wp_posts AS p
	INNER JOIN wp_postmeta AS meta ON p.ID = meta.post_id
		AND meta.meta_key = 'start_time'
	INNER JOIN wp_term_relationships AS artist_tr ON p.ID = artist_tr.object_id
	INNER JOIN wp_term_taxonomy AS artist_tax ON artist_tr.term_taxonomy_id = artist_tax.term_id
		AND artist_tax.taxonomy = 'artist'
WHERE
	p.post_type = 'event'
	AND p.ID != 74
	AND p.post_status = 'active'
	AND artist_tr.term_taxonomy_id IN(11, 10, 9, 8)
GROUP BY p.ID
ORDER BY
	meta.meta_value ASC

And now we have all the artists ids returned.