I need to finalize this MySQL multiple table JOIN

Question

I have entires, equipments, brands, times and seasons.

entries:

• id
• time
• equipment_1
• equipment_2

equipments:

• id
• id_brand

brands:

• id
• name

times:

• id
• id_season

seasons:

• id
• name

My actual SQL query is:

SELECT entries.*, times.id_season AS id_season
FROM entries, seasons
WHERE entries.time = times.id

But in the final query I need the next information that I don't know how to obtain it:

1. The name for each entries.equipment_ as equipment_1_name and equipment_2_name which is set in brands.name.
2. The name of the season as season_name.

Thank you in advance!

Answer 1

Assuming you have normalized data. This avoid costly cartesian joins. I never use cartesian joins myself, although there are some cases where they are useful. Not here, though.

SELECT
  entries.*,
  times.id_seasons AS id_season,
  b1.name AS equipment_1_name,
  b2.name AS equipment_2_name,
  seasons.name AS season_name
FROM entries
LEFT JOIN equipments AS equipments_1
  ON equipments_1.id = entries.equipment_1
LEFT JOIN brands AS brands_1 
  ON brands_1.id = equipments_1.id_brand
LEFT JOIN equipments AS equipments_2 
  ON equipments_2.id = entries.equipment_2
LEFT JOIN brands AS brands_2
  ON brands_2.id = equipments_2.id_brand
LEFT JOIN times 
  ON times.id = entries.time
LEFT JOIN seasons 
  ON seasons.id = times.id_season;