Hello,
SQL is not a reporting and formatting tool,
but if you wish - why not ?
create table users(
userid int primary key auto_increment,
name varchar(20)
);
insert into users( name ) value ('Bob'), ('Adam'), ('Martin') ;
create table books(
bookid int primary key auto_increment,
title varchar(50)
);
insert into books( title ) value ('Harry Potter'), ('Random Cookbook'),
('How to surprise a woman'), ('The Lord of the Rings') ;
create table userbooks(
userid int,
bookid int,
CONSTRAINT PRIMARY KEY (userid, bookid),
CONSTRAINT FOREIGN KEY (userid) REFERENCES users( userid ),
CONSTRAINT FOREIGN KEY (bookid) REFERENCES books( bookid )
);
insert into userbooks (userid, bookid) value (1,1), (1,3),
(2,2), (2,3), (2,4), (3,1), (3,3),(3,4);
SELECT case when xx.bookid is null
then concat( userid, ' - ', xx.name )
else concat( ' ', xx.title)
end result
FROM (
SELECT u.userid, u.name, ub.bookid, b.title
FROM users u
JOIN (
SELECT userid, bookid FROM userbooks
UNION ALL
SELECT userid, null FROM users
) ub
ON ub.userid = u.userid
LEFT JOIN books b
ON ub.bookid = b.bookid
order by u.userid, ub.bookid
) xx;
+ ------------------------------- +
| result |
+ ------------------------------- +
| 1 - Bob |
| Harry Potter |
| How to surprise a woman |
| 2 - Adam |
| Random Cookbook |
| How to surprise a woman |
| The Lord of the Rings |
| 3 - Martin |
| Harry Potter |
| How to surprise a woman |
| The Lord of the Rings |
+ ------------------------------- +
11 rows