On Jun 9, 2:08*pm, Nathan G <nat...@factory8.com> wrote:
> I need help writing a query that returns productIds for products that
> are in the same order as a given productId. (Customers who purchased
> this also bought...)
>
> I have three tables, here they are with their relevant columns.
>
> ORDERS
> oid
>
> ORDERS_CONTENT
> ocid
> oid
> pid
>
> PRODUCTS
> pid
>
> I need to select all pid (product Ids) from orders_content where a
> product Id (we'll use #5 for this example) is in that same order.
>
> So if someone places an order for products #5 and #12, the query would
> return pid 12.
>
> Thanks!

I think I figured it out, although I'm afraid this will run slow after
thousands of orders come in.

Also, I made it so it doesn't return the product your are finding
matches for, and orders them by popularity

SELECT
`pid` as `productId`,
count(`pid`) as `popularity`
FROM
`orders_content`
WHERE
`oid` IN (
SELECT
`oid`
FROM
`orders_content`
WHERE
`pid` = 5
) AND
`pid` != 5
GROUP BY
`pid`
ORDER BY
count(`pid`) DESC;