Sql, sqlite SELECT with inner join

Question

I am wondering how to select one column twice using internal hosting in a different way. my database is sqlite and i am using the PDO db driver.

My example:

SELECT orders.id, orders.order_number, clients.first_name, clients.last_name, users.name AS user_name FROM orders INNER JOIN clients ON orders.client_id = clients.id INNER JOIN users ON orders.created_by = users.id 

I want to also get the name of the user who edited this post

  orders.edited_by = users.id 

How to join this choice?

Answer 1

You will need to use table aliases.

 SELECT orders.id, orders.order_number, clients.first_name, clients.last_name, creator.name AS creator_user_name editor.name AS editor_user_name FROM orders INNER JOIN clients ON orders.client_id = clients.id INNER JOIN users creator ON orders.created_by = creator.id INNER JOIN users editor ON orders.edited_by = editor.id 

Answer 2

Use aliases in your table names so that you can use multiple references to the same table. It can also facilitate reading large queries.

 SELECT orders.id, orders.order_number, clients.first_name, clients.last_name, createUsers.name AS creator_name, editUsers.name AS editor_name FROM orders INNER JOIN clients ON orders.client_id = clients.id INNER JOIN users As createUsers ON orders.created_by = users.id INNER JOIN users As editUsers ON orders.edited_by = users.id 

You can use as many "instances" of the same table as you want.