SQL select. Help please

Question

Hi!

I have a 3 tables

1. tBooks

id Title authorID

2. tAuthors

id Name

3. TBookAuthors

bookID authorID

a book can have a few authors, an author can write a few books

So i need to search a book by name and select all the authors in the one record.

i tried like this, but result is a few records (how much authors). Only diference between this record is the name of author.

select a.[ID], a.[title], c.[name] 
from tBooks a
    inner join tBookAuthors b
    on a.[ID] = b.bookID
    inner join tAuthors c
    on b.[authorID] = c.[ID]
where title like '%Blen%'

Answer 1

It would really help if you posted which SQL DBMS you are using.

In MySQL for example you can use GROUP_CONCAT-

Answer 2

Change your inner joins to outer joins, you probably have books with no corresponding tBookAuthors or tAuthors records.

Since you are applying a where clause filter, how many books are you actually expecting to get back? Run this query to find out:

select count(*)
from tBooks 
where title like '%Blen%'

Answer 3

You might try using a left join instead of an inner join to join a and b.

SELECT a.ID, a.title, c.name
FROM tBooks a
    LEFT JOIN tBookAuthors b
    ON a.ID = b.bookID
    LEFT JOIN tAuthors c
    ON b.authorID = c.ID
WHERE a.title LIKE '%Blen%'

Answer 4

SELECT a.name, b.title FROM tBookAuthors x INNER JOIN tAuthors a ON a.id = x.AuthorID 
INNER JOIN tBooks b ON x.BookID = b.id
WHERE b.Title LIKE '%this book title%';

You don't need the authorID column in the tBooks table either.

Answer 5

Try this.

 SELECT a.ID, a.title, c.name
 FROM tBooks a
 LEFT JOIN tBookAuthors b ON a.ID = b.bookID
 left JOIN tAuthors c ON b.authorID = c.ID
 WHERE a.title LIKE '%Blen%'