SQL: Searching grand child table for specifc items

Question

Consider following schema:

Customers:
Col      | Type    |
-------------------|
id       | INTEGER |
name     | VARCHAR |

Orders:
Col         | Type    |
----------------------|
id          | INTEGER |
customer_id | INTEGER |
date        | DATE    |

Items
Col         | Type    |
----------------------|
id          | INTEGER |
order_id    | INTEGER |
name        | VARCHAR |
quantity    | INTEGER |

And here's the breif run down of the schema: Customer can have many orders, and an order can have many items Note: You may argue that the items should refer to some products table with a product_id, but let it be simple as is now.

From my database, I want to ask following question:

For a given customer, list all the items that start with La.

My version of the query goes like this:

SELECT * FROM items WHERE name LIKE 'La%'

This would return ALL the items that Begin with La for ALL the customers. What I want is that I get all the items for a SPECIFIC customer with say id 1. How do I write such a query? My second version that would do is like this:

SELECT * FROM items WHERE name LIKE 'La%' AND order_id in (SELECT id FROM orders WHERE customer_id=1);

But is their any other efficient/elegant or a more better way to do the same thing?

Answer 1

The elegant way to do this is called a join.

SELECT
  c.id            customer_id,
  c.name          customer_name,
  i.id            item_id,
  i.name          item_name,
  COUNT(i.id)     order_count,
  SUM(i.quantity) items_ordered
FROM
  customers c
  INNER JOIN orders o ON c.id = o.customer_id
  INNER JOIN items  i ON i.id = o.order_id
WHERE
  i.name LIKE 'La%'
  AND c.id = 1
GROUP BY
  c.id,
  c.name,
  i.id,
  i.name

Answer 2

Sub-SELECT is one approach, another would be to JOIN the tables and query that.

Something like this, my SQL is very rusty but I think this should work.

SELECT c.Name, o.id, o.date, i.name, i.quantity FROM Customer AS c
LEFT OUTER JOIN Orders AS o ON c.id = o.customer_id
LEFT OUTER JOIN Items AS i on o.id = i.order_id
WHERE i.name LIKE 'La%' AND c.id = 1

Answer 3

select i.name from customers c
join oders o
on c.id = o.customer_id
join Items i
on o.id = i.order_id where c.id = WHATEVER_CUSTOMER_ID_YOU_WANT
and i.name LIKE 'La%'