SQL Case Query with n-to-m relationship

Question

I have a 500 line SQL query I'm working on so I'll try to use a simpler example....

Assume I have a table Books that has Book_id, Author and Title columns, a table Publisher that has publisher information and a table Translations that contains Book_id, Title and Language columns.

I want to SELECT all books from a certain publisher regardless of whether or not they've been translated into Spanish, but I do want to show a column that contains true if they have a Spanish translation.

So assume that my query is SELECT Author, Title, Spanish FROM Books, Translations WHERE Publisher.Book_id = Books.Book_id AND Translations.Book_id = Books.Book_id AND Publisher_id = 1. How do I get a true in the Spanish column from a table that can contain the same book several times or no times ?

Answer 1

Learn to use proper, explicit, standard JOIN syntax! I do hope that you don't have 500 lines of code using commas for joins.

You want a left join and case expression:

SELECT b.Author, b.Title, 
       (CASE WHEN t.Book_id IS NOT NULL THEN 'true' ELSE 'false' END) as spanish_flag
FROM Books b LEFT JOIN
     Translations t
     ON t.Book_id = b.Book_id AND
        t.language = 'Spanish'  
WHERE b.Publisher_id = 1;

Note: This uses a string representation for 'true'/'false'. You haven't specified the database. Some have direct support for booleans.

Actually, a suppose a book could have multiple translations into a single language. A more natural way to write the query uses EXISTS:

SELECT b.Author, b.Title, 
       (CASE WHEN EXISTS (SELECT 1
                          FROM Translations t
                          WHERE t.Book_id = b.Book_id AND
                                t.language = 'Spanish'
                         ) 
             THEN 'true' ELSE 'false'
        END) as spanish_flag
FROM Books b
WHERE b.Publisher_id = 1;