|
-
Jul 12th, 2009, 03:25 PM
#1
Thread Starter
Lively Member
COUNT query
I'm having trouble with my query.
Code:
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|