This recently came up and is an easy way to view related data from four tables. In this case I have a task table relating to a user table, group table, and a category table.
The query goes something like this.
select tasks.*,category.name as cat ,concat(users.firstname,’ ‘,users.lastname) as username,groups.name as groupname from tasks LEFT JOIN category ON tasks.category=catetory.catid LEFT JOIN users ON tasks.userid=users.userid LEFT JOIN groups ON tasks.groupid=groups.groupid where taskid=1.
Let’s break this down
Select tasks.* (select all columns from tasks and at the end where the task id=1)
select category name as cat (this is because, in this query, more than one table has a column named name where the id stored in tasks for category equal the index in the category table)
select concat(users.firstname,’ ‘,users.lastname) (concatenate the first and last name with a space in the middle from users where the id for the user stored in tasks equal the index for the user in the users table)
select groups.name as groupname (firstly we are renaming name to groupname as there is more than one table with a column named name and we want to select group where the id for groups stored in tasks equal the index in the groups table)