MySQL Forums
Forum List  »  MySQL Query Browser

Re: Get information from multiple tables in one single query
Posted by: kordirko kordirko
Date: January 02, 2012 06:54PM

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

Options: ReplyQuote


Subject
Written By
Posted
Re: Get information from multiple tables in one single query
January 02, 2012 06:54PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.