Postgresql – “Print” specific columns through foreign keys

foreign keyjoin;postgresql

I have three tables:

  • Users
  • Users_request
  • Users_applications

Table Users has the id column, that column is related on Users_request.id finally Users_request.id is related with Users_applications.id

I try to do:

Users table has a column called name and User_applications has application_name.
I want to "print" only those columns with their rows through JOIN clause.

Desired Output:

 user| application_name|
-----|------------------
user1| application1
user2| application2
userN| applicationN

I have the next, but I want improve it not using hardcode Users_applications.id 

SELECT Users.id, Users_applications.name 
  FROM Users, Users_applications 
 WHERE Users.id = 'User1' AND User_applications.id = n ;

Is there any other way to accomplish it, through only Users.id using JOIN clauses?

Thanks in advance.

Best Answer

Table Users has the id column, that column is related on Users_request.id finally Users_request.id is related with Users_applications.id

So Users.id = Users_request.id and Users_request.id = Users_applications.id which means that Users.id = Users_applications.id

Then you can do:

SELECT Users.id, Users_applications.name 
FROM Users INNER JOIN Users_applications  ON Users.id = Users_applications.id
WHERE Users.id = 'User1';
Related Question