humbledust humbledust - 1 month ago 6
SQL Question

Multiple Join on same column

I have two tables one is the

category
table with fields as

id , name, created_by, created_date, modify_by, modify_date, status


(please note created_by & modify_by will have ids of users from user table)

and other table
user
with columns as

id, name, role and many more


I have to list category on web page with names of those users who created the new category and also those who modified the category

I am using following SQL query to get created_by user name and also modify_by user name to display in list

BEGIN
SELECT cat.id,cat.name, cate.name as parentname, cat.parent_id,
cat.created_by, cat.created_date, cat.modify_by, cat.modify_date,
user.firstname
FROM `category`as cat
INNER JOIN `user` ON cat.created_by = user.id
LEFT JOIN category as cate ON cate.id = cat.parent_id;
END


What should I add to this query to get amemodifier user n from its id which can be different from creator id?

Or do I have to write separate query for getting modifier name?

Answer

If am not wrong, you need to Join USER table twice

SELECT cat.id,
       cat.NAME,
       cat.NAME   AS parentname,
       cat.parent_id,
       cat.created_by,
       cat.created_date,
       cat.modify_by,
       cat.modify_date,
       c.firstname AS created_user,
       m.firstname AS modified_user
FROM   categoryas cat
       LEFT JOIN USER c
              ON cat.created_by = c.id
       LEFT JOIN USER m
              ON cat.modify_by = m.id; 

If the created_by column will never be NULL then you can change the LEFT JOIN USER c to INNER JOIN USER c

Comments