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
x
+--------+--------+---------+-------------+
| 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 |
+----+--------------+---------------+---------+
Advertisement
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)