Sql query which will return the column from two or more tables

Question

Suppose I have Author(id, name), Book(id, name), Book_Author(bookid, authorid), Book_Reader table(bookId, authorId) tables.

AUthor(id, name)
========

    1  | James
    2  | Ivan
    3  |John
    4  |Harry

Book(id, name)
=============

    1  |   Introducing Mysql
    2  |   Hibernate tutorials


Book_Author(bookId, authorId)
=========

    1  | 1
    2  | 3

Book_reader(bookId, authorId)
=============

        1  | 1
        1  | 3
        1  | 4

where bookAuthor table represents the author of the book and book reader represents the authors read that book.

Now I want the query which will return me book, author of the book and authors list read that book.

Bookname             Author         Readers
================================================

    Introducing Mysql     James           James,John,Harry

Or I want all the books where author is James or Reader is James.. Thanks,

http://stackoverflow.com/questions/662207/mysql-results-as-comma-separated-list — Nalaka526, May 07 '14 at 08:43
You have to try and ask for help, try with join sql requests, try that: http://www.tizag.com/sqlTutorial/sqljoin.php — Houssam Badri, May 07 '14 at 08:43

Abhik Chakraborty · Answer 1 · 2014-05-07T11:23:36.040

select
b.name,
a1.name as author,
group_concat(a2.name) as reader
from 
book_reader br 
inner join book b on b.id = br.bookId
inner join book_Author ba on ba.bookId = br.bookId
inner join author a1 on a1.id = ba.authorId
inner join author a2 on a2.id = br.authorId
group by b.id

DEMO

I want all the books where james is author or james is reader

For the above you can just use where condition as

select
name,
type
from
(
  select
  b.name,
  'Author' as type
  from book b
  inner join book_author ba on ba.bookId = b.id
  inner join author a on a.id = ba.authorId
  where a.name = 'James'
  UNION ALL
  select
  b.name,
  'Reader' as Type
  from book b
  inner join book_reader br on br.bookId = b.id
  inner join author a on a.id = br.authorId
  where a.name = 'James'
)t

DEMO

I have added UNION ALL to list all the and also type as column to notify Reading or Author. If you do not want that just remove and also can Use just UNION just in case you dont want duplicate book names.

I want all the books where james is author or james is reader — pbhle, May 07 '14 at 11:03
Check the update. Also dont put new requests on the existing question what you asked earlier is totally different than what r asking now. So put a different question in this cases. — Abhik Chakraborty, May 07 '14 at 11:26

score 0 · Answer 2 · answered May 07 '14 at 09:04

Book, author, and readers:

SELECT b.name AS BookName, a1.name AS AuthorName, group_concat(DISTINCT a2.name) AS readers
FROM book_reader AS br 
JOIN book AS b on b.id = br.book_id
JOIN book_author AS ba on ba.book_id = br.book_id
JOIN author AS a1 on a1.id = ba.author_id
JOIN author AS a2 on a2.id = br.author_id
GROUP BY b.id;

I've used table aliases in this query to reduce the amount of typing. The GROUP_CONCAT() function is used to group together the reader names into one column.

The books where the author is James:

SELECT book.name
FROM book_author
JOIN book ON book.id = book_author.book_id
JOIN author ON author.id = book_author.author_id
WHERE author.name = "James";

The books where the reader is James:

SELECT book.name
FROM book_reader
JOIN book ON book.id = book_reader.book_id
JOIN author ON author.id = book_reader.author_id
WHERE author.name = "James";

Sql query which will return the column from two or more tables

2 Answers2