How to select a table that have one to another and another table relationship

I don’t know if the question make sense or not. I’m trying to request information from 2 different tables that are linked (via FK) to a table and display the data with information from that linked tables.

post

+--------+--------+---------+-------------+  
| postid | userid | title   | description |  
+--------+--------+---------+-------------+  
| 1      | 1      | example | example     |  
+--------+--------+---------+-------------+  
| 2      | 2      | example | example     |  
+--------+--------+---------+-------------+  
| 3      | 3      | example | example     |  
+--------+--------+---------+-------------+

user

+--------+----------+--------+  
| userid | username | roleid |  
+--------+----------+--------+  
| 1      | admin    | 1      |  
+--------+----------+--------+  
| 2      | helper   | 1      |  
+--------+----------+--------+  
| 3      | test     | 2      |  
+--------+----------+--------+

role

+--------+---------------+  
| roleid | role          |  
+--------+---------------+  
| 1      | administrator |  
+--------+---------------+  
| 2      | default       |  
+--------+---------------+

Is there a way to select/show this table?:
result

+----+--------------+---------------+---------+
| id | creator_name | creator_role  | title   |
+----+--------------+---------------+---------+
| 1  | admin        | administrator | example |
+----+--------------+---------------+---------+
| 2  | helper       | administrator | example |
+----+--------------+---------------+---------+
| 3  | test         | default       | example |
+----+--------------+---------------+---------+

Answer

Try this:

    select a.postid id, b.username creator_name, c.role creator_role, a.title
    from `post` a
    left join `user` b on (a.userid = b.userid)
    left join `role` c on (b.roleid = c.roleid)

Advertisement

Answer