Purchased products A and B

[The Little Guy]

I need a query that gets a list of member id who purchased products 1 and 87. I can not figure out the best way to do this. How can I do this?

 

Here is my table structure:

+----------------+------------------+------+-----+-------------------+-----------------------------+
| Field          | Type             | Null | Key | Default           | Extra                       |
+----------------+------------------+------+-----+-------------------+-----------------------------+
| recurring_id   | int(11)          | NO   | PRI | NULL              | auto_increment              |
| member_id      | int(11)          | NO   | MUL | 0                 |                             |
| product_id     | int(10) unsigned | YES  | MUL | NULL              |                             |
| special        | enum('ap')       | YES  |     | NULL              |                             |
| is_enabled     | tinyint(1)       | NO   | MUL | 0                 |                             |
| processor_id   | int(11)          | NO   | MUL | 0                 |                             |
| member_card_id | int(11)          | NO   |     | 0                 |                             |
| date_updated   | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------------+------------------+------+-----+-------------------+-----------------------------+

 

 

I thought that this query would work, but it doesn't:

select * from recurring where product_id in (87,1) group by member_id order by member_id limit 10;

 

Thanks for any help!

[Muddy_Funster]

I take it the problem with your current query is that it's returning an OR dataset?

[Muddy_Funster]

I'm thinking along the lines of:

SELECT * from recurring where ((member_id IN (SELECT member_id FROM recurring WHERE product_id = 87)) AND (member_id IN (SELECT member_id FROM recurring WHERE product_id = 1))) GROUP BY member_id ORDER BY member_id LIMIT 10