select from orders where product purchased by same customer

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

select from orders where product purchased by same customer

Post by Luke »

I need to do a query on my orders table for anybody who bought a specific product more than once. I understand how to do the joins on the cart items and order tables, but I am not so sure how I would only select those who have bought a product more than once.

For instance, let's assume my table looks like this, and the specific product is "book".

Code: Select all

orderid         buyerID         orderdate           item
            
1           1               2/15/2008           rock
2           2               2/15/2008           cheese
3           3               2/19/2005           book
4           3               3/5/2008            book
5           4               2/15/2008           book
6           5               9/21/2007           milk
7           6               7/14/2007           book
8           6               4/23/2006           book
9           6               6/6/2003            chapstick
With the given data, I'd want my query to return rows 3,4,7 & 8. I'd want these because the same person bought "book" multiple times. How would I go about this?
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: select from orders where product purchased by same customer

Post by papa »

Do I missunderstand something ?

select where buyerID = 3 and item = book
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: select from orders where product purchased by same customer

Post by VladSun »

[sql]SELECT    orders.*FROM     ordersINNER JOIN    orders AS repeated_orders    ON        orders.item = repeated_orders.item        AND       orders.buyerID = repeated_orders.buyerID       AND        orders.orderID != repeated_orders.orderID[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Re: select from orders where product purchased by same customer

Post by Luke »

papa wrote:Do I missunderstand something ?

select where buyerID = 3 and item = book
yep you are missing something. I want to find all records where somebody has bought "book" more than once. So... I can't specify buyerID, because that's what I'm LOOKING for.

Vladsun... thanks I'll give it a try...
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: select from orders where product purchased by same customer

Post by josh »

Select * from A where A.fk IN ( select `customer_id`, count( `id` ) as c from B where c > 1 )

( didnt check for correctness but I've used subqueries for this, the join approach looks interesting though. )
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: select from orders where product purchased by same customer

Post by Weirdan »

Code: Select all

 
SELECT buyerID FROM orders
WHERE item="book"
GROUP BY buyerID
HAVING count(*) > 1
 
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: select from orders where product purchased by same customer

Post by VladSun »

josh wrote:Select * from A where A.fk IN ( select `customer_id`, count( `id` ) as c from B where c > 1 )

( didnt check for correctness but I've used subqueries for this, the join approach looks interesting though. )
Because you want to reffer to aggregate function in a WHERE clause you have to use GROUP BY and HAVING clauses. And GROUP BY will require a filesort which is slow and no indexies could be used.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: select from orders where product purchased by same customer

Post by VladSun »

Weirdan wrote:

Code: Select all

 
SELECT buyerID FROM orders
WHERE item="book"
GROUP BY buyerID
HAVING count(*) > 1
 
It's OK but you still have to show the whole rows which will transform your query into josh's one :)
Luke wrote:I'd want my query to return rows 3,4,7 & 8.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: select from orders where product purchased by same customer

Post by Weirdan »

VladSun wrote: It's OK but you still have to show the whole rows which will transform your query into josh's one :)
Luke wrote:I'd want my query to return rows 3,4,7 & 8.
Missed that part.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: select from orders where product purchased by same customer

Post by josh »

But it does use indexes.

EXPLAIN SELECT *
FROM a
WHERE a
IN (

SELECT b
FROM b
)
( with index on table a column a and table b column b )
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY a index NULL a 4 NULL 10352 Using where; Using index
2 DEPENDENT SUBQUERY b index_subquery b b 4 func 1 Using index

Without any indexes
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY a ALL NULL NULL NULL NULL 10352 Using where
2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 351 Using where

Even if you do::
EXPLAIN SELECT *
FROM a
WHERE a
IN ( 500, 501, 502 )
It tells me it is using the index.

My previous query was incorrect though since you can only select 1 column for a subquery in an IN clause.

EXPLAIN SELECT a, count( a ) AS count
FROM a
GROUP BY a
ORDER BY `count` DESC

10352 Using index; Using temporary; Using filesort ( this inner query does examine all rows though, as would be expected, does the having method overcome this, I couldn't imagine how )
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: select from orders where product purchased by same customer

Post by VladSun »

josh wrote:But it does use indexes.
Yes, your query does use indexies.
But if you have to rewrite this query according to the requirements in the OP (by including the Weirdan's query), I don't think it will use indexies (i.e. it will use filesort for at least one step).
Please, write the full query needed by the OP using your subquery approach.
There are 10 types of people in this world, those who understand binary and those who don't
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: select from orders where product purchased by same customer

Post by josh »

VladSun wrote:Please, write the full query needed by the OP using your subquery approach.
Dunno. I'm not trying to argue. You could always issue 2 separate queries. Build an array of ids. use implode() and generate the 2nd query. Not saying that would scale up though, I don't think a filesort is the end of the world.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: select from orders where product purchased by same customer

Post by Benjamin »

I think this is the best solution guys...

Code: Select all

 
SELECT
  o.buyerID
FROM
  orders o
WHERE
   o.item = 'book'
  AND EXISTS(SELECT o2.orderid FROM orders o2 WHERE o2.buyerID = o.buyerID AND o2.orderid != o.orderid AND o2.item = o.item LIMIT 1)
GROUP BY
  o.buyerID
ORDER BY
  o.orderdate ASC
 
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: select from orders where product purchased by same customer

Post by VladSun »

Some benchmarks:

[sql]CREATE TABLE  `orders` (  `orderId` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,  `buyerId` int(10) UNSIGNED NOT NULL,  `item` int(10) UNSIGNED NOT NULL,  PRIMARY KEY  (`orderId`),  KEY `IX_buyerId` (`buyerId`),  KEY `IX_item` (`item`),  KEY `IX_buyerId_item` (`buyerId`,`item`),  KEY `IX_All` (`orderId`,`buyerId`,`item`)) ENGINE=MyISAM AUTO_INCREMENT=5424766 DEFAULT CHARSET=utf8;[/sql]

[sql]SELECT count(*) FROM orders;[/sql]
420016
My query (note that I had a bug in my previous query, that's why I put DISTINCT. Otherwise in case there are more than 2 identical orders, the result will be doubled):
[sql]SELECT SQL_NO_CACHE    DISTINCT orders.*FROM    ordersINNER JOIN    orders AS repeated_orders    ON       orders.item = repeated_orders.item       AND       orders.buyerID = repeated_orders.buyerID       AND       orders.orderID <> repeated_orders.orderIDWHERE  orders.item = 80[/sql]
46 rows fetched in 0.0020s (0.0031s)
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","orders","ref","IX_buyerId,IX_item,IX_buyerId_item","IX_item","4","const",106,"Using temporary"
1,"SIMPLE","repeated_orders","ref","IX_buyerId,IX_item,IX_buyerId_item","IX_buyerId_item","8","tt.orders.buyerId,const",1,"Using where; Distinct"
astions's query
[sql]SELECT SQL_NO_CACHE  orders.*FROM  ordersWHERE  orders.item = 80  AND  EXISTS(    SELECT SQL_NO_CACHE      repeated_orders.orderid    FROM      orders AS repeated_orders    WHERE      repeated_orders.buyerID = orders.buyerID      AND      repeated_orders.orderid != orders.orderid      AND      repeated_orders.item = orders.item    LIMIT      1  )[/sql]
46 rows fetched in 0.0020s (0.0031s)
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"PRIMARY","orders","ref","IX_item","IX_item","4","const",106,"Using where"
2,"DEPENDENT SUBQUERY","repeated_orders","ref","IX_buyerId,IX_item,IX_buyerId_item","IX_buyerId_item","8","tt.orders.buyerId,const",1,"Using where"
josh-Weirdan query
[sql]SELECT SQL_NO_CACHE  orders.*FROM  ordersWHERE  buyerId IN  (    SELECT SQL_NO_CACHE      buyerId    FROM      orders    WHERE      item=80    GROUP BY      buyerID    HAVING      count(*) > 1  )  AND    item=80[/sql]
46 rows fetched in 0.0025s (0.1714s)
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"PRIMARY","orders","ref","IX_item","IX_item","4","const",106,"Using where"
2,"DEPENDENT SUBQUERY","orders","ref","IX_item","IX_item","4","const",106,"Using where; Using temporary; Using filesort"
If you feel like it's not what you meant, please tell me.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply