mySQL using same table/fields multiple times in a single query

Question

I have 2 tables in mySQL database :

customers
============
customer_id (1, 2 )
customer_name (john, mark)


orders
============
order_id = 123
customer_id = 1
customer_from_id = 2

Idea is to do single query on orders table joining customers table whereby

orders.customer_id = customers.customer_id 
orders.customer_from_id = customers.customer_id

to get the "customer_name" by JOIN(ing) two tables.

So how do i do single query on "orders" and expand all (2) "customer_name" fields so result looks like this :

+--------+------------+---------------------+------------------+---------------------+
order_id  customer_id   customer_order_name   customer_from_id   customer_from_name
+--------+------------+---------------------+------------------+---------------------+
   123       1                  john                  2                 mark  
+--------+------------+---------------------+------------------+---------------------+

It means using same table 2x in a query and aliasing output field "customer_name" 2x with "customer_order_name" and "customer_from_name".

It shall be simple but i am stuck. Any help would be much appreciated.

Thank You.

What have you tried so far? Show what JOIN queries you've tried and we'll let you know where your going wrong — Lee
– Lee, Commented Jul 16, 2013 at 10:19

Erik Ekman · Accepted Answer · 2013-07-16 10:20:05Z

1

Join twice and use prefix and give aliases:

select order_id, buyer.customer_id, buyer.customer_name, seller.customer_id as customer_from_id, seller.customer_name as customer_from_name from orders o
join customers seller on o.customer_from_id = seller.customer_id
join customers buyer on o.customer_id = buyer.customer_id;

answered Jul 16, 2013 at 10:20

Erik Ekman

2,08612 silver badges13 bronze badges

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

Comments

Praveen Prasannan · Accepted Answer · 2013-07-16 10:24:38Z

0

select order_id,  c1.customer_id as customer_id,
c1.customer_name as customer_order_name ,
c2.customer_id as customer_from_id,
c2.customer_name as customer_from_name
from orders o
left join customers c1 using (customer_id)
left join customers c2 on o.customer_from_id = c2.customer_id;

fiddle

answered Jul 16, 2013 at 10:24

Praveen Prasannan

7,15310 gold badges53 silver badges71 bronze badges

3 Comments

user2193006 Over a year ago

Thank You all for Your answers. Will try it out and report back. My error was on second LEFT JOIN . I used again just LEFT JOIN customers USING (customer_id); and got 1st column right but in second i got either same values or NULL. ( Amazed with Your fast responses. Thanks again.)

user2193006 Over a year ago

Booth solutions work perfectly. Solved. (Don't believe it was so simple and i missed it, aliasing orders as o and then using LEFT JOIN second time on "o.customer_from_id = c2.customer_id;") One more Q. Do i need to mark this as CLOSED / SOLVED or do the Admins do that ?

Praveen Prasannan Over a year ago

You should accept the answer which really helped you to solve your problem. Once you accept the answer, it will be considered as closed. Also you can upvote all the answers which helped you.

Collectives™ on Stack Overflow

mySQL using same table/fields multiple times in a single query

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related