Tricky SQL

marque

I know there probably is a better place to ask this, but most people here are very cluey when it comes to SQL statements.

I am trying to figure out how to do a MySQL statement that will fetch similar orders that other customers bought.

I have a table called products2orders inside is the following relative fields:

Order_ID
Product_ID

both fields obviously have relationships to other tables. It works in 3rd normalization with many products to many orders. So an Order may contain any amount of products.

In the SQL I want to give a Product_ID and fetch the top 3 products that were bought with that product. This would probably be done by sending the Product_ID to get the Order_ID's that product was in, then getting the other products from those orders and ordering the rows by how many times those products appear in the orders.

I really hope that makes sense.

I'm running MySQL 4.1 so that may help a little with selects in where clauses, etc.

Can anyone help?

bpat1434

Check the mySQL website (http://www.mysql.com). Under the development section, if you look at joins, I think that would help you.

Basically, what you're trying to do (from what I get) is that you want something like:

$query = 'SELECT * FROM `products`, `orders`'.
'WHERE products.id = orders.prod_id';

Something like that. I think. Not 100% but that is a starting point. Basically what that does is selects all rows from both tables where the value in the column id in table products is equal to the column prod_id in table orders.

You should read the mySQL documentation.
This should help you:
http://dev.mysql.com/doc/mysql/en/JOIN.html

~Brett

marque

Thanks bpat1434,

But not exactly. To explain further in this scenario there are 3 tables:

products
orders
products2orders

the 3rd normalization comes on the products2orders table with contains has a relationship to the other two tables. I don't need to access any data from the other two tables unless I want any specific information from them. I'll make a join later to get the name and the price of the products from the products table later. But for now I just need the id's (for testing).

Here is what I have so far after reading my own post:

SELECT t2.Product_ID,
       t1.Order_ID,
       COUNT(t2.Product_ID)
FROM products2orders AS t1,
     products2orders AS t2
WHERE t1.Product_ID = '25'
AND t2.Order_ID = t1.Order_ID
GROUP BY (t2.Order_ID)

The table is joining on itself inorder to get the products. What I am lost with now is what to group it on. I get stupid data with the count I have at the moment.

bpat1434

Ok, you explained that a bit more.

Have you tried using the error_reporting(E_ALL); and perhaps losing the count() function, and add the line mysql_num_rows()?

Would that suffice?

~Brett

marque

I'm not sure what getting any errors will do for me. The sql i wrote does work and gives me a result.

Any way I think I have got it working.

This is the final:

SELECT t2.Product_ID,
       t1.Order_ID,
       products.Title,
       products.Retail
FROM products2orders AS t1,
     products2orders AS t2
LEFT JOIN products ON t2.Product_ID = products.Product_ID
WHERE t1.Product_ID = '25'
      AND t2.Order_ID = t1.Order_ID
AND t2.Product_ID != '25'
GROUP BY (Order_ID) DESC
LIMIT 3

That seems to work (I think), as in it gives me a decent result. As you can see I don't want the same product I am referencing from to come up in the result and I droped the COUNT because it was confusing me. It was returning figures like 30 where it should only give me about 3 or so.

Anyway the GROUP BY(Order_ID) ordering returns in the right order even though the COUNT was showing up strange results.

If you or anyone can come up with a better solution please let me know. But for now I think I have resolved it.

It helps to put things in perspective sometimes.