CODEWITHSUNDEEP
mysqlselectjoinrows
ma9ma
ma9ma

Reputation: 41

MYSQL: How to split left join results in multiple rows

I have some data in a table like so:

user

user_id  name
1        John
2        William
3        cindy
4        Julian
5        Monique

books out

user_id  book_id
1        1
2        3
3        5
5        10

vinyl out

user_id  vinyl_id
1        6
2        7
3        8
4        9

I want a single SELECT statement that would produce this output:

user_id  item_out
1        1 
1        6
2        3
2        7
3        5     
3        8 
4        9
5        10

So, in order of user_id and book_id/vinyl_id. Is this possible?

Upvotes: 3

Views: 1256

Answers (2)

Priyanshu
Priyanshu

Reputation: 881

Try This Please comment if any error. 

select u.user_id,b.book_id as item_out from user u left join 
 books_out b on u.user_id = b.user_id 
 UNION ALL
 select u.user_id,v.vinyl_id as item_out from user u left join 
 vinyl_out v on u.user_id = v.user_id 
 order by user_id;

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You have to UNION ALL results from two separate queries:

SELECT u.user_id, book_id AS item_out
FROM user AS u
INNER JOIN books_out AS b ON u.user_id = b.user_id

UNION ALL

SELECT u.user_id, vinyl_id AS item_out
FROM user AS u
INNER JOIN vinyl_out AS v ON u.user_id = v.user_id
ORDER BY user_id, item_out

The first query returns results from books_out table, whereas the second returns results from vinyl_out table.

Note: ORDER BY clause is applied to the result set that is produced by the UNION ALL operation.

Demo here

Upvotes: 2

Related Questions