MySQL Use Distinct or Group By in XREF Table Query

Question

I'm building a php configurator with a series of relationships which I'm controlling with MySQL XREF tables.

There is one XREF table which has multiple dependencies as below:

Table: cto_body_deck_rear_chassis_xref

body_id	deck_type_id	rear_id	chassis_id
22	20	23	13
23	20	18	17
23	20	21	17
23	20	24	17
24	20	18	17
25	21	22	14

Each complete combination is unique although there are similarities between columns; however, I'm getting a duplication problem when selecting from a deck type table, relative to a body id variable passed in the URL.

Table: cto_deck_type

deck_type_id	deck_type_content
20	Single Deck
21	3/4 Length Fixed 2nd Deck
22	Full Length Fixed 2nd Deck

If I use the following MySQL statement:

SELECT d.deck_type_id, d.deck_type_content
FROM cto_deck_type d
LEFT JOIN cto_body_deck_rear_chassis_xref xref
ON xref.deck_type_id = d.deck_type_id
WHERE xref.body_id = 23

I get 3 results, even though each result is identical because the body_id and deck_type_id match 3 times (20).

If the results are identical, I want to group them together or select distinct but I'm not sure what the statement should look like?

Any assistance would be appreciated.

Answer 1

SELECT d.deck_type_id, d.deck_type_content FROM cto_deck_type d LEFT JOIN cto_body_deck_rear_chassis_xref xref ON xref.deck_type_id = d.deck_type_id WHERE xref.body_id = 23 ;; and add the line GROUP BY d.deck_type_id, d.deck_type_content