Reputation: 1995
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?
Upvotes: 1
Views: 10191
Reputation: 2716
As others have mentioned, the problem you faced was with your ON condition. To specifically answer your question:
In MySQL, if you omit a JOIN, an INNER JOIN/CROSS JOIN is used. For other databases, it is different. For example, PostgreSQL uses a CROSS JOIN, not an INNER JOIN.
Re: http://dev.mysql.com/doc/refman/5.7/en/join.html
"In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise."
Upvotes: 0
Reputation: 242140
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.
Upvotes: 9
Reputation: 18081
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.
Upvotes: 4
Reputation: 19637
Check your SQL. Your where clause cannot possibly be books
.shelfNum
=books
.shelfNum
And what are all those single quotes for?
Upvotes: 1
Reputation: 103155
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.
Upvotes: 0
Reputation: 19637
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)
Upvotes: 5