Sql query to select several items from a another table in relation to values on a single row of a table

orderTb: **id_order** **qty** **id_user** **updatedby**
           1             5         2            1

userTb:  **id** **name**
           1      paul
           2      mark

These are my two tables, wish do a mysql query to return: id_order,qty,id_user as name1 and updatedby as name2. I’ve tried this query but not working:

select orderTb.id_order,orderTb.qty,userTb.name as name1,userTb.name as Name2 
from orderTb,userTb where orderTb.id_user=userTb.id OR 
orderTb.updatedby=userTb.id

Answer

Use this.

SELECT o.id_order, o.qty, u1.name as name1, u2.name as name2 
FROM orderTb o
INNER JOIN userTb u1 ON u1.id=o.id_user
INNER JOIN userTb u2 ON u2.id=o.updatedby;

See the demo here: DB-Fiddle

Advertisement

Answer