Select query return dublicated result-CodePudding

When i run a query like that

SELECT
    order_id,
    thread_id,
    color_select
FROM
    order_threads
WHERE
    order_id = 42

I got the result like that

order_id	thread_id	color_select
42	1	dark grey
42	1	off white
42	7	off white
42	3	dark grey

I need to get more data from other tables so i rewrite the query to became like that

SELECT
    order_threads.thread_id,
    order_threads.order_id,
    order_threads.color_select,
    thread.name,
    patron.id,
    patron.order_id,
    patron_colors.color_name AS color_name,
    patron_colors.id,
    patron_colors.color_count
FROM
    order_threads
JOIN thread ON order_threads.thread_id = thread.id
JOIN patron ON patron.order_id = order_threads.order_id
JOIN patron_colors ON patron_colors.patron_id = patron.id
WHERE
    order_threads.order_id = '42'

The results are dublicated like that

thread_id	order_id	color_select	name	id	order_id	color_name	id	color_count
1	42	dark grey	blue thread	51	42	dark grey	74	2
1	42	dark grey	blue thread	51	42	off white	75	2
1	42	off white	blue thread	51	42	dark grey	74	2
1	42	off white	blue thread	51	42	off white	75	2
7	42	off white	green thread	51	42	dark grey	74	2
7	42	off white	green thread	51	42	off white	75	2
3	42	dark grey	white thread	51	42	dark grey	74	2
3	42	dark grey	white thread	51	42	off white	75	2

It should be like that

thread_id	order_id	color_select	name	id	order_id	color_name	id	color_count
1	42	dark grey	blue thread	51	42	dark grey	74	2
1	42	off white	blue thread	51	42	dark grey	74	2
7	42	off white	green thread	51	42	off white	75	2
3	42	dark grey	white thread	51	42	dark grey	74	2

Where is my Error?

CodePudding user response：

There's no mistake in your query, the problem is that some patrons have more than one color available so you have to specify in the query that you only want to select the rows where the color of the patron matches the color of the order.

Replace

"WHERE order_threads.order_id = '42'" with

"WHERE order_threads.order_id = '42' AND order_threads.color_select = patron_colors.color_name"