Finding a Bouquet by Flower Variety

The Complete Query

SELECT DISTINCT bouquets.name, bouquets.price
FROM bouquets
JOIN flowers ON bouquets.id = flowers.bouquet_id
JOIN flower_varieties ON flowers.variety_id = flower_varieties.id
WHERE flower_varieties.variety = 'Carnation'
ORDER BY bouquets.name
LIMIT 1;
    

Understanding the Problem

We need to find the first bouquet (alphabetically) that contains at least one Carnation flower. This is similar to a real-world scenario where a customer asks: "What's your least expensive bouquet that includes roses?" The challenge is that we need to:

1. Connect bouquets to their flowers
2. Check the variety of each flower
3. Find bouquets with matching flowers
4. Return only one result
5. Make sure it's the first alphabetically

Breaking Down the Query

The Three Tables

Our query connects three tables in a chain:

bouquets → flowers → flower_varieties
(names,    (links     (master list
 prices)    them)      of varieties)
    

Real World Analogy

Think of this like finding a recipe that uses a specific ingredient:

• bouquets table = cookbook (collection of recipes)
• flowers table = ingredient list for each recipe
• flower_varieties = master ingredient database
• The JOINs are like flipping through the cookbook and checking ingredients

Query Components Explained

1. SELECT Statement

SELECT DISTINCT bouquets.name, bouquets.price

The DISTINCT keyword is crucial here because:

• A bouquet might have multiple Carnations
• Without DISTINCT, we'd get duplicate rows for each Carnation
• Example: "Get Well Soon" has two Carnations but should appear once

2. First JOIN

JOIN flowers ON bouquets.id = flowers.bouquet_id

This JOIN connects each bouquet to its flowers. It's like opening each recipe and seeing its ingredients. We use INNER JOIN because:

• We only want bouquets that have flowers
• Empty bouquets wouldn't match our search anyway

3. Second JOIN

JOIN flower_varieties ON flowers.variety_id = flower_varieties.id

This JOIN identifies what type each flower is. It's like looking up each ingredient in a master list to confirm it's a Carnation.

4. WHERE Clause

WHERE flower_varieties.variety = 'Carnation'

This filters for only Carnations. Note that:

• It uses the full table name to be explicit
• Case sensitivity matters for the string match
• The test specs show it works with other varieties too

5. Ordering and Limiting

ORDER BY bouquets.name
LIMIT 1;

These ensure we get exactly what we want:

• ORDER BY sorts alphabetically
• LIMIT 1 returns only the first match

Data Flow Example

1. Start with bouquets:
   id | name           | price
   1  | Get Well Soon | 59.99
   2  | Mother's Day  | 39.99

2. Join with flowers:
   bouquet_id | variety_id | color
   1          | 1          | yellow
   1          | 1          | white
   2          | 1          | pink
   2          | 1          | purple

3. Join with flower_varieties:
   name           | price | variety
   Get Well Soon  | 59.99 | Carnation
   Get Well Soon  | 59.99 | Carnation
   Mother's Day   | 39.99 | Carnation
   Mother's Day   | 39.99 | Carnation

4. After DISTINCT:
   name           | price
   Get Well Soon  | 59.99
   Mother's Day   | 39.99

5. After ORDER BY and LIMIT:
   name           | price
   Get Well Soon  | 59.99
    

Alternative Approaches

Here are other ways to write this query:

-- Using EXISTS
SELECT name, price
FROM bouquets b
WHERE EXISTS (
    SELECT 1
    FROM flowers f
    JOIN flower_varieties v ON f.variety_id = v.id
    WHERE f.bouquet_id = b.id
    AND v.variety = 'Carnation'
)
ORDER BY name
LIMIT 1;

-- Using a subquery in WHERE
SELECT name, price
FROM bouquets
WHERE id IN (
    SELECT DISTINCT bouquet_id
    FROM flowers f
    JOIN flower_varieties v ON f.variety_id = v.id
    WHERE v.variety = 'Carnation'
)
ORDER BY name
LIMIT 1;
    

Common Pitfalls

When writing queries like this, watch out for:

• Forgetting DISTINCT and getting duplicates
• Missing the LIMIT and getting too many results
• Using WHERE instead of JOIN for table relationships
• Not using full table names and causing ambiguity

Performance Considerations

This query is optimized because:

• Uses proper JOINs instead of subqueries
• Filters early in the execution plan
• Uses indexed columns for joins (id fields)
• Limits results to exactly what's needed