I'm wondering how to select one column twice using an inner joinor some other way. my database is sqlite and i use 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 get also, the user_name who edited this record
orders.edited_by = users.id
How to join this selection?
SQLite supports different types of SQL Joins, like INNER JOIN, LEFT OUTER JOIN, and CROSS JOIN. Each type of JOIN is used for a different situation as we will see in this tutorial.
The SQLite Inner join is the most common type of join. It is used to combine all rows from multiple tables where the join condition is satisfied. The SQlite Inner join is the default type of join.
In SQLite, the NATURAL JOIN is such a join that performs the same task as an INNER or LEFT JOIN, in which the ON or USING clause refers to all columns that the tables to be joined have in common. A natural join joins two tables by their common column names.
You'll 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With