-2

I want to be able to list all items that have been purchased in every order for all orders where an item within that order is a specific item.

Eg: I want to be able to do a search for all orders where tomato ketchup has been ordered and list all the products in these orders.

I want to be able to say, therefore, for all customers that purchased tomato ketchup, what other products accompanied that item.

Its similar to what you would expect to see in any cyber market place where "others also bought..."

The database I am using has a table with products and an order ID. I imagine the simplest way is to write a PHP script to perform 2 queries, one to return all orders containing the item in question and another to list all items for each of the order IDs. But I had hoped there was a "clever" query that could replace that.

My ultimate goal would be to search for all orders where a list of items exist. The idea is to try and whittle down the results to only show those where the specified combination of products has been found.

John Conde
  • 217,595
  • 99
  • 455
  • 496
Andrew Fernando
  • 3
  • 3

1 Answers1

0

Assuming:

products(id integer, name varchar(25) and orders (id integer) and order_items (order_id integer, product_id integer)

Use this:

SELECT oi.order_id, p2.name
  FROM products p
    INNER JOIN order_items oi
      ON p.id = oi.product_id
    INNER JOIN order_items oi2
      ON oi.order_id = oi2.order_id
    INNER JOIN products p2
      ON oi2.product_id = p2.id
    WHERE p.name = 'Carrot'

Fiddle example: http://sqlfiddle.com/#!9/0b9f4/2

pala_
  • 8,901
  • 1
  • 15
  • 32
  • That is exactly what I was hoping for. Thanks so very much. If I could ask one more thing? How then, would I get a set of orders, displaying all information from all orders where a list of items exist in the same list? – Andrew Fernando Apr 04 '15 at 13:51
  • I'll get back to you on that one soon. – pala_ Apr 04 '15 at 14:03
  • You may be interested in this question: http://stackoverflow.com/questions/29431729/select-result-that-match-all-array-values/29432206#29432206 I think it's pretty much the same thing you're asking – pala_ Apr 04 '15 at 17:40