查询:交叉产品而不是连接

我有两个表,我想加入,但我收到MySQL的错误

Table: books
bookTagNum ShelfTagNum
book1      1
book2      2
book3      2

Table: shelf
shelfNum   shelfTagNum
1          shelf1
2          shelf2

我希望我的结果是:

bookTagNum ShelfTagNum shelfNum
book1      shelf1           1
book2      shelf2           2
book3      shelf2           2

但相反,我也得到了额外的结果:

book1      shelf2           2

我认为我的查询是在进行交叉产品而不是连接:

SELECT `books`.`bookTagNum` , `books`.`shelfNum` , `shelf`.`shelfTagNum` , `books`.`title`
FROM books, shelf
where `books`.`shelfNum`=`books`.`shelfNum`
ORDER BY `shelf`.`shelfTagNum` ASC
LIMIT 0 , 30

我究竟做错了什么？

我想你想要的

where `books`.`shelfTagNum`=`shelf`.`shelfNum`

为了匹配books和shelf表中的行,您需要在where子句中包含每个行的术语- 否则,您只需要对行执行无操作检查books,因为每行都shelfNum将等于它shelfNum.

正如@ fixme.myopenid.com建议的那样,你也可以去显式JOIN路线,但这不是必需的.

如果你想确定你正在进行连接而不是跨产品,你应该在SQL中明确说明它,因此:

SELECT books.bookTagNum,books.shelfNum, shelf.shelfTagNum, books.title
FROM books INNER JOIN shelf ON books.shelfNum = shelf.shelfTagNum
ORDER BY shelf.shelfTagNum

(它只返回两个表中存在的那些行),或者:

SELECT books.bookTagNum,books.shelfNum, shelf.shelfTagNum, books.title
FROM books LEFT OUTER JOIN shelf ON books.shelfNum = shelf.shelfTagNum
ORDER BY shelf.shelfTagNum

(将返回书籍中的所有行),或者:

SELECT books.bookTagNum,books.shelfNum, shelf.shelfTagNum, books.title
FROM books RIGHT OUTER JOIN shelf ON books.shelfNum = shelf.shelfTagNum
ORDER BY shelf.shelfTagNum

(将从架子返回所有行)