0

I am having some bother in selecting books that are neither translated books, books series' nor book sections. The structure looks like this. (sorry, stackoverflow won't let me post an image with less than 10 reputation)

It uses class inheritance design pattern with shared primary keys.

This is what I've came up with so far:

SELECT *
FROM (
        (
            (`hedwig_db`.`refs` JOIN `hedwig_db`.`books` 
                        ON `refs`.`ref_id`=`books`.`ref_id`
            )
        LEFT JOIN `hedwig_db`.`authors`
                ON `refs`.`ref_id` =  `authors`.`ref_id`
        )
    LEFT JOIN `hedwig_db`.`editors`
            ON `books`.`book_id`=`editors`.`book_id`
    )
WHERE `books`.`book_id`
NOT IN (SELECT `books`.`ref_id`,
                `books`.`book_id`,
                `book_section`.`book_id`,
                `book_translated`.`book_id`,
                `book_series`.`book_id`
        FROM(
                (
                    (`hedwig_db`.`books` JOIN `hedwig_db`.`book_translated`
                        ON `books`.`book_id`=`book_translated`.`book_id`
                    )
                JOIN `hedwig_db`.`book_section`
                    ON `books`.`book_id`=`book_section`.`book_id`
                )
            JOIN `hedwig_db`.`book_series`
                ON `books`.`book_id`=`book_series`.`book_id`
            )
    )

It's the NOT IN query that is returning nothing and I can't for the life of me figure out how to get what I'm looking for! Thanks in advance :)

Community
  • 1
  • 1
TheCurlyProgrammer
  • 27
  • 8

1 Answers1

0

I would simplify the where clause to just use not exists:

SELECT *
FROM `hedwig_db`.`refs` r JOIN
     `hedwig_db`.`books` b 
     ON r.ref_id = b.ref_id LEFT JOIN
     `hedwig_db`.`authors` a
     ON r.ref_id =  a.ref_id LEFT JOIN
     `hedwig_db`.`editors` e
     ON b.book_id = e.book_id
WHERE NOT EXISTS (select 1 from `hedwig_db`.`book_translated` bt where bt.book_id = b.book_id) and
      NOT EXISTS (select 1 from `hedwig_db`.`series` bs where bs.book_id = b.book_id) and
      NOT EXISTS (select 1 from `hedwig_db`.`book_sections` bs where bs.book_id = b.book_id);

Your not in clause is joining the tables together, so it would be finding books that are all three. You could do a union all instead, but I would stick with three different conditions.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for the response! I've just tried this out but the result set is the same as when the query `SELECT * FROM books JOIN authors ON ... JOIN editors ON ...` runs. Which means all books are selected rather than the books that are just books (i.e not translated, series nor section) – TheCurlyProgrammer Aug 24 '14 at 14:07
  • Ooops. Those conditions should have been connected by `and` and not `or`. – Gordon Linoff Aug 24 '14 at 14:08