COUNT query

[Eclyps19]

I'm having trouble with my query.

SELECT tblItems.title, tblItems.imagepath, COUNT(*) as count
FROM tblItems, bought, bought as alsobought 
WHERE bought.ISBN = ' & ISBN & ' 
AND alsobought.ISBN != bought.ISBN 
AND tblItems.ISBN = alsobought.ISBN 
GROUP BY tblItems.ISBN HAVING count > 1 
ORDER BY count DESC 
LIMIT 4;

my tables are as follows:
tblItems has info about the book - ISBN being the Primary Key, title, author, etc.

bought has two columns, one for the transaction number (id) and one for the item that was purchased (ISBN). This table looks like this
1,1234567890123
1,9876543210987
1,0879543569842
2,8544468046023
2,8800097856128
etc

You know how amazon gives you suggestions when you look at an item based on how many other ppl bought that item as well as another item? That's what I'm trying to achieve. Unfortunately my count field is giving me extremely high numbers - higher than the total number of rows in my tables.

Can anyone tell what I'm doing wrong?

[leinad31]

Did you check the result of the matrix from join before doing the aggregation? If counts are too high then the logic of the join is wrong.

[si_the_geek]

The logic is wrong, which would be clearer if you had used 'modern' join syntax rather than merging it into the Where clause, eg:

...
FROM tblItems
INNER JOIN bought as alsobought ON (tblItems.ISBN = alsobought.ISBN)
INNER JOIN bought               ON (alsobought.ISBN != bought.ISBN)

WHERE bought.ISBN = ' & ISBN & '

GROUP BY ...

There is a valid link between tblItems and AlsoBought - and running just that part of the query will list all items ever bought.

However, the way that part of the query links to Bought (which is a different table to AlsoBought, despite them having the same initial source table) is almost a Cartesian product - every item in AlsoBought is linked to every item in Bought (so if there are 100 rows, that would be 100*100 = 10000 results), but you do at least exclude the Bought rows with identical ISBN's (so 100*99 = 9900).


What you should do is run the query without aggregation (the Group By etc), and return a few fields from the other tables. That way you should be able to see what is missing from your join conditions (it is a field which you have mentioned).