Home / Questions / 293766
Accepted
amblador
Asked: 2020-09-14 18:28:34 +0800 CST 2020-09-14 18:28:34 +0800 CST

SELECT query with two foreign keys that refer to the same table in Postgre

  • 772

I have two tables in PostgreSQL as follows:

Table "users", with the following fields:

  • user_id : incremental integer, and primary key of the table
  • user_name : varchar

and another "data" table with the following fields:

  • data_id: incremental integer, and primary key
  • description: varchar
  • created_by: integer and foreign key of user_id of the users table
  • modified_by: integer and foreign key of user_id of the users table

The query I want to obtain is the data_id, description, and user_name of the users referenced in created_by and modified_by

What I tried was the following:

SELECT data_id, descripcion, users.user_name FROM data 
LEFT JOIN users ON users.user_id = data.created_by
LEFT JOIN users ON users.user_id = data.modified_by

and it gave me the error I expected:

table name "users" specified more than once

In addition to that selectI am calling user_nameonly once, and not twice as it should be, but I have no idea what else to do.

sql

2 Answers

  1. Best Answer

    Try as follows:

    • Give the table an alias usersfor each time you invoke it in theLEFT JOIN
    • In SELECTit you will choose more than once from the same table the desired values, but instead of doing it to the table usersyou would do it to one of the 2 aliases that you placed in said table

    Code

    SELECT data_id, 
       descripcion, 
       users1.user_name AS userCreatedBy,
       users2.user_name AS userModifieddBy
FROM data 
LEFT JOIN users AS users1 ON users1.user_id = data.created_by
LEFT JOIN users AS users2 ON users2.user_id = data.modified_by
    • 1

  2. you have to use aliases.

    SELECT data_id, descripcion, users.user_name, users_two.user_name FROM data 
LEFT JOIN users ON users.user_id = data.created_by
LEFT JOIN users as users_two ON users_two.user_id = data.modified_by
    • 0