I'm having trouble wrapping my head around how to write this query.

A hypothetical problem that is that same as the one I'm trying to solve:

Say I have a table of apples. Each apple has numerous attributes, such as color_id, variety_id and the orchard_id they were picked from.

The color_id, variety_id, and orchard_id all refer to their respective tables: colors, varieties, and orchards.

Now, say I need to query for all apples that have color_id = '3', which refers to yellow in the colors table.

I want to somehow obtain this yellow value from the query.

Make sense?

Here's what I was trying:

SELECT * FROM apples, colors.id WHERE color_id = '3'
LEFT JOIN colors ON apples.color_id = colors.id