MySQL JOIN query with result in multiple rows

Question

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.

KaeL · Accepted Answer · 2016-09-07 09:42:32Z

3

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;

edited Sep 7, 2016 at 9:42

answered Sep 7, 2016 at 7:44

KaeL

3,6612 gold badges31 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Strawberry Over a year ago

The super query is redundant.

KaeL Over a year ago

Updated my answer. Thanks @Strawberry

Strawberry Over a year ago

...as are the parentheses

Priyanka Patil · Accepted Answer · 2016-09-07 08:10:29Z

0

    SELECT 
      user_id as Id, user_name as Name
      tbl_user   

        UNION

    SELECT admin_id
        , admin_name
        FROM tbl_admin

    ORDER BY Id;

Use UNION

answered Sep 7, 2016 at 8:10

Priyanka Patil

177 bronze badges

Collectives™ on Stack Overflow

MySQL JOIN query with result in multiple rows

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related