CODEWITHSUNDEEP
mysqlsql
Arif
Arif

Reputation: 1211

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.

Upvotes: 0

Views: 43

Answers (2)

KaeL
KaeL

Reputation: 3659

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;

Upvotes: 3

Priyanka Patil
Priyanka Patil

Reputation: 17

    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

Upvotes: 0

Related Questions