Arif Arif - 3 months ago 6
MySQL Question

MySQL JOIN query with result in multiple rows

I have 2 MySQL tables

tbl_user


user_id | user_name
1 | John
2 | Kevin


tbl_admin


admin_id | admin_name
1 | Bob

SELECT `tbl_user`.`user_id` as `ID`, `tbl_user`.`user_name` as `NAME`,
`tbl_admin`.`admin_id` as `ID`, `tbl_admin`.`admin_name` as `NAME`
FROM `tbl_admin` INNER JOIN `tbl_user` ORDER BY `tbl_user`.`creation_date` DESC


Below is the result from my above query

ID | NAME | ID | NAME
1 | John | 1 | Bob
2 | Kevin | 1 | Bob


I want the result in the below format

ID | NAME |
1 | John |
1 | Bob |
2 | Kevin |


I can write separate query for each table but in that case TABLE 1 result will load first and then TABLE 2 result but my goal is to get both TABLE results in a single query ORDER by ID or DATE.

Answer

Use UNION ALL

(SELECT user_id AS Id
    , user_name AS Name
    FROM tbl_user)

UNION ALL

(SELECT admin_id
    , admin_name
    FROM tbl_admin)

ORDER BY Id;