query: cross product instead of join

Question

I have two tables that I would like to join but I am getting an error from MySQL

Table: books
bookTagNum ShelfTagNum
book1      1
book2      2
book3      2

Table: shelf
shelfNum   shelfTagNum
1          shelf1
2          shelf2

I want my results to be:

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

but instead I am also getting an extra result:

book1      shelf2           2

I think my query is doing a cross product instead of a join:

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

What am I doing wrong?

Answer 1

I think you want

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

In order to match rows from the books and shelf tables, you need to have terms from each in your where clause - otherwise, you're just performing a no-operation check on the rows of books, since every row's shelfNum will be equal to its shelfNum.

As @fixme.myopenid.com suggests, you could also go the explicit JOIN route, but it's not necessary.

Answer 2

Try this:

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

Because the implicit JOIN condition was not properly stated the result was a cross product.

Answer 3

Check your SQL. Your where clause cannot possibly be books.shelfNum=books.shelfNum

And what are all those single quotes for?

Answer 4

Hi Steve, if you want to be sure you're doing a join instead of a cross product, you should state it explicitly in the SQL, thus:

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

(which will return only those rows which exist in both tables), or:

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

(which will return all rows from books), or:

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

(which will return all rows from shelf)

Answer 5

FYI: If you rewrite your names to be consistent, things get a lot easier to read.

Table 1: Book
BookID     ShelfID  BookName
1          1        book1
2          2        book2
3          2        book3

Table 2: Shelf
ShelfID    ShelfName
1          shelf1
2          shelf2

now, a query to extract books to shelves is

SELECT 
 b.BookName,
 s.ShelfName
FROM
 Book b
JOIN Shelf s ON s.ShelfID = b.ShelfID

---

To answer the original question:

> where `books`.`shelfNum`=`books`.`shelfNum`
>        ^^^^^--------------^^^^^------------- books repeated - this is an error

the WHERE clause, as written, does nothing, and because your where clause isn't limiting any rows, you are indeed getting the cross product.