Is it possible to write a SQL query to return specific rows, but then join some columns of those rows with another table?

Question

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

Answer 1

This is how you do the join in general. It will return all apples whether or not a color is specified:

SELECT * 
FROM apples a
LEFT OUTER JOIN colors c ON a.color_id = c.id

If you only want yellow apples, it should probably be an inner join, since you are requiring that a.color_id not be null in your where clause:

SELECT * 
FROM apples a
INNER JOIN colors c ON a.color_id = c.id
WHERE a.color_id = 3

Update:

SELECT *  
FROM apples a 
INNER JOIN colors c ON a.color_id = c.id 
INNER JOIN orchards o ON a.orchard_id = o.id 
WHERE a.color_id = 3 and a.orchard_id = 5

Answer 2

SELECT apples.*, color.name
FROM apples
JOIN colors ON apples.color_id = colors.id
WHERE colors.id = 3

Answer 3

SELECT a.*, c.ColorName
FROM Apples a INNER JOIN Colors c ON a.color_id = c.color_id
WHERE c.color_id = 3