MySQL SQL statement with INNER JOIN

Question

I have the following SQL statement

SELECT be.*, it.order_number
FROM songs AS be

INNER JOIN
(
    SELECT song_id, order_number
    FROM items
    WHERE order_status = 1
) it
ON be.id = it.song_id

INNER JOIN orders AS or
ON it.order_number = or.order_number

WHERE be.active = 0

I can't seem to understand why this statement does not produce any results. When I remove the following line;

INNER JOIN orders AS or
ON it.order_number = or.order_number

It seems to produce results, and I know that the order_number does exist in the orders table - so it's clearly incorrect syntax but i'm not sure where to go from here? Appreciate the help.

Have you considered the possibility that "or" might be a reserved word? — Ian McLaird
– Ian McLaird, Commented Sep 24, 2012 at 22:35

Ian McLaird · Accepted Answer · 2012-09-24 22:47:28Z

1

The problem in this particular instance is that the or in the query is a reserved word. You can use that if you wish, but you'll have to quote it, like so

SELECT be.*, it.order_number
FROM songs AS be

INNER JOIN
(
    SELECT song_id, order_number
    FROM items
    WHERE order_status = 1
) it
ON be.id = it.song_id

INNER JOIN orders AS "or"
ON it.order_number = "or".order_number

WHERE be.active = 0

Generally though, for readability, I'd avoid such names. If you have to quote it or escape it, it's probably a bad name.

answered Sep 24, 2012 at 22:47

Ian McLaird

5,5852 gold badges25 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL SQL statement with INNER JOIN

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related