mysql - sql查询不拉记录相同

Question

我有这个问题:

SELECT Book.title, Book.copyright, Book.publisher, Book.id,
    MaterialType.type, Item.id as item_id, 100 as relevance FROM `books` AS `Book`
inner JOIN `material_types` AS `MaterialType` ON (`MaterialType`.`id` = `Book`.`material_type_id`)
inner JOIN `items` AS `Item` ON (`Item`.`book_id` = `Book`.`id` AND `Item`.`accession_number` = '0936')
WHERE 1 = 1  GROUP BY `Book`.`id`

什么也没拉起来。但是，如果我执行此查询，它会找到正确的记录:

SELECT * FROM `items` AS `Item` WHERE `Item`.`accession_number` = '0936'

不过最奇怪的是，它可以用于其他记录。如果我使用 accession_number 0396，如果在两个查询中都找到正确的记录。我一辈子都弄不明白发生了什么事。非常感谢任何帮助。

Answer 1

您需要将 LEFT JOIN 与 matierial_types 一起使用，因为该表可能没有与书籍相同的 materialType id试试这个

SELECT Book.title, Book.copyright, Book.publisher, Book.id,
    MaterialType.type, Item.id as item_id, 100 as relevance FROM `books` AS `Book`
LEFT JOIN `material_types` AS `MaterialType` ON (`MaterialType`.`id` = `Book`.`material_type_id`)
LEFT JOIN `items` AS `Item` ON (`Item`.`book_id` = `Book`.`id`) 
WHERE `Item`.`accession_number` = '0936'
GROUP BY `Book`.`id`