MySQL -- joining then joining then joining again? Yuck.

Question

MySQL setup: step by step.

programs -> linked to --> speakers (by program_id)

At this point, it's easy for me to query all the data:

SELECT * 
FROM programs 
JOIN speakers on programs.program_id = speakers.program_id

Nice and easy.

The trick for me is this. My speakers table is also linked to a third table, "books." So in the "speakers" table, I have "book_id" and in the "books" table, the book_id is linked to a name.

I've tried this (including a WHERE you'll notice):

SELECT *
FROM programs
JOIN speakers on programs.program_id = speakers.program_id
JOIN books on speakers.book_id = books.book_id
WHERE programs.category_id = 1
LIMIT 5

No results.

My questions:

1. What am I doing wrong?
2. What's the most efficient way to make this query?

Basically, I want to get back all the programs data and the books data, but instead of the book_id, I need it to come back as the book name (from the 3rd table).

Thanks in advance for your help.

UPDATE:
(rather than opening a brand new question)

The left join worked for me. However, I have a new problem. Multiple books can be assigned to a single speaker.

Using the left join, returns two rows!! What do I need to add to return only a single row, but separate the two books.

Thank you!!!

Answer 1

is there any chance that the books table doesn't have any matching columns for speakers.book_id?

Try using a left join which will still return the program/speaker combinations, even if there are no matches in books.

SELECT *
FROM programs
JOIN speakers on programs.program_id = speakers.program_id
LEFT JOIN books on speakers.book_id = books.book_id
WHERE programs.category_id = 1
LIMIT 5

Btw, could you post the table schemas for all tables involved, and exactly what output (or reasonable representation) you'd expect to get?

Edit: Response to op author comment

you can use group by and group_concat to put all the books on one row.

e.g.

SELECT speakers.speaker_id, 
       speakers.speaker_name, 
       programs.program_id, 
       programs.program_name,
       group_concat(books.book_name)
FROM programs
JOIN speakers on programs.program_id = speakers.program_id
LEFT JOIN books on speakers.book_id = books.book_id
WHERE programs.category_id = 1
GROUP BY speakers.id
LIMIT 5

Note: since I don't know the exact column names, these may be off

Answer 2

That's typically efficient. There is some kind of assumption you are making that isn't true. Do your speakers have books assigned? If they don't that last JOIN should be a LEFT JOIN.

This kind of query is typically pretty efficient, since you almost certainly have primary keys as indexes. The main issue would be whether your indexes are covering (which is more likely to occur if you don't use SELECT *, but instead select only the columns you need).