I keep getting error code 1248 every derived table must have its own alias

Question

I keep getting error code 1248 every derived table must have its own alias on the following and cannot figure out why. I have tried rewriting several times to see if I can catch where I am making a mistake but I guess I am overlooking something.

SELECT order_id, order_date, c.customer_id, last_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id,
(
    SELECT phone
    FROM addresses a
    JOIN orders od ON a.customer_id = od.customer_id
)
WHERE address_id = billing_address_id AND ship_date IS NULL;

I don't think you can even put anything where you have the derived table — Phil, May 05 '14 at 04:10
actually **READING** the error would be of use. it tells you exactly what the problem is. — Marc B, May 05 '14 at 04:10
@MarcB Yes I know. Having it after the join condition is what I'm questioning. Having `JOIN ... ON , ` is just invalid syntax — Phil, May 05 '14 at 04:12

score 0 · Answer 1 · answered May 05 '14 at 04:10

I am not sure what you are trying to do is, but I think your problem is you haven't given aliases to your subquery ,which is required in JOIN

SELECT order_id, order_date, c.customer_id, last_name
FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id,
(SELECT phone
FROM addresses a
    JOIN orders od ON a.customer_id = od.customer_id) as subTable
WHERE address_id = billing_address_id AND ship_date IS NULL;

score 0 · Answer 2 · answered May 05 '14 at 04:37

Guess your looking for something like this (don't know if I got all the fields right, but make sure you double check; next time give table structures please) :

SELECT
o.`order_id`,
o.`order_date`,
o.`customer_id`,
c.`last_name`,
a.`phone`
FROM
orders AS o 
LEFT JOIN customers AS c ON o.customer_id = c.customer_id
LEFT JOIN addresses AS a ON (
    o.customer_id = a.customer_id
    AND a.address_id = o.billing_address_id
)
WHERE
ship_date IS NULL;

I keep getting error code 1248 every derived table must have its own alias

2 Answers2