jeudi 4 juin 2015

How do I join two tables based on the ids stored in a related table with SQLite?

I have a table "books" with columns id, title, length. I have another table of genres with id,name. Finally, I have a third table that used to related the two called bookGenres with columns id,bId,gId.

I want to run a select query that gets genres and books based on the bookGenres table.

I tried something like

SELECT 
book.name AS bookName,
book.length AS bookLength,
genres.name AS bookGenre
FROM
book
LEFT JOIN bookGenres ON book.id = bookGenres.bId
LEFT JOIN bookGenres ON genres.id = bookGenres.gId

but that doesn't return any results. I got an error that genres.name isn't a column. When I remove it I'm told bookGenres.bId is an ambiguous column name.

Aucun commentaire:

Enregistrer un commentaire