Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sql, sqlite SELECT with inner join

Tags:

sql

sqlite

pdo

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?

like image 247
sulest Avatar asked Jan 11 '12 15:01

sulest


People also ask

Does SQLite support inner join?

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.

What is SQLite inner join *?

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.

Does SQLite support natural 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.


1 Answers

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
like image 126
David M Avatar answered Nov 14 '22 23:11

David M