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!

Nathan G 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.
This is not what you described above.
"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 I would expect the query to return #5 and #12.

Also, I don't understand how the PRODUCTS table is relevant to this query.

On Jun 9, 2:52*pm, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
> Nathan G 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.
>
> This is not what you described above.
> "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 I would expect the query to return #5 and #12.
>
> Also, I don't understand how the PRODUCTS table is relevant to this query.

You are correct, as i stated, it would return 12 and 5. The products
table isnt relevant except to show thats where pid's come from.

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;

Nathan G wrote:
> 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;

Hmmm, you should be using self joins with appropriate indexes.

On Jun 9, 3:10*pm, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
> Nathan G wrote:
> > 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;
>
> Hmmm, you should be using self joins with appropriate indexes.

please elaborate

Nathan G wrote:
> On Jun 9, 3:10 pm, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
>> Nathan G wrote:
>> > 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;
>>
>> Hmmm, you should be using self joins with appropriate indexes.
>
> please elaborate

A bit busy right now. Will do you an example in the morning.

Nathan G wrote:
[snip]

>> Hmmm, you should be using self joins with appropriate indexes.
>
> please elaborate

If I may be so bold: I think Paul is suggesting that you look up 'Self
Joins' in the MySQL documentation or on Google as a learning exercise.

Dan Rumney wrote:
> Nathan G wrote:
> [snip]
>
>>> Hmmm, you should be using self joins with appropriate indexes.
>>
>> please elaborate
>
> If I may be so bold: I think Paul is suggesting that you look up 'Self
> Joins' in the MySQL documentation or on Google as a learning exercise.

No, I will offer a solution, just a bit busy with paid work at the moment.

On 9 Jun, 21:42, Nathan G <nat...@factory8.com> wrote:
> On Jun 9, 3:10*pm, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
>
>
>
>
>
> > Nathan G wrote:
> > > 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;
>
> > Hmmm, you should be using self joins with appropriate indexes.
>
> please elaborate- Hide quoted text -
>
> - Show quoted text -

Right, now I'm still not totally sure whether you in fact whant what
you initially described:
"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."

Or whether you want what you have coded above whereby: "it doesn't
return the product your are finding matches for".

I will assume the latter.
The query below should be efficient, provided you have a composite
index on (pid,oid)
SELECT
o1.pid productId,
count(o1.pid) popularity
FROM orders_content o1
JOIN orders_content o2 ON o1.oid = o2.oid AND o2.pid = 5
WHERE o1.pid != 5
GROUP BY o1.pid
ORDER BY popularity DESC