MYSQL JOIN with AND error

Question

I'm trying to pull first_name from Names table, but only if that users_id is in the Orders table and order_num = 1, but I keep getting an error.

SELECT first_name
FROM customers
LEFT JOIN orders
ON orders.order_id = customers.cust_id 
AND orders.order_num = 1

but my query is returning multiple values instead of just one, like it ignoring orders.order_num=1 - does my query look right, or is there something I'm doing wrong?

Perhaps this chart can be of help: http://stackoverflow.com/questions/4715677/difference-between-left-join-and-right-join-in-sql-server/4715847#4715847 — Daan Timmer, May 09 '12 at 11:11

score 2 · Accepted Answer · answered May 09 '12 at 11:09

2

SELECT first_name
FROM customers
LEFT JOIN orders
ON orders.order_id = customers.cust_id 
WHERE orders.order_num = 1

answered May 09 '12 at 11:09

WojtekT

4,735
25
37

I've been wrestling around with joins for hours. I thought that the last `WHERE` clause would be for the first table? Anyway it works though! – Derp May 09 '12 at 11:11
2

Hey @Derp, please tag if this is solution to your problem. Motivates people to keep giving great answers! – Guido Gautier May 09 '12 at 11:15
Yeah, I guess there's a 10 minute timelimit - wouldn't let me xD - TY WojtekT – Derp May 09 '12 at 11:26

score 0 · Answer 2 · answered May 09 '12 at 11:09

0

AND orders.order_num = 1

should be

WHERE orders.order_num = 1

answered May 09 '12 at 11:09

codaddict

445,704
82
492
529

score 0 · Answer 3 · answered May 09 '12 at 11:11

Try this.

SELECT first_name
FROM customers
INNER JOIN orders
ON (orders.cust_id = customers.id)
WHERE orders.order_num = 1
GROUP BY customers.id

INNER JOIN will only include record where the ON condition matches

WHERE limits the results to only where order_num = 1

GROUP BY makes sure you only get 1 result for each customer

MYSQL JOIN with AND error

3 Answers3