agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Richard Klingler <richard@klingler.net>
To: pgsql-sql@lists.postgresql.org
Subject: Re: Clean up shop database
Date: Wed, 19 Jan 2022 13:40:59 +0100
Message-ID: <20220119134059286538.4c015b19@klingler.net> (raw)
In-Reply-To: <9713782b-9c29-2497-be30-6dd1af0d550c@gmail.com>
References: <20220119120317479683.54287f97@klingler.net>
<9713782b-9c29-2497-be30-6dd1af0d550c@gmail.com>
Odd...gives me the same result....
Tried another approach as the ordered is known where to start
from....but still the same:
select p.productid as id, p.name_de as name
from product p, orderitems i,orders
where p.productid = i.orderitems2productid
and i.orderitems2orderid = orders.orderid
and orders.orderid < 14483
and p.pieces < 1
and p.active = 't'
group by id
order by id desc
Still lists products after January 1st 2021...but I know what is going
on....
For example the query above returns as the first product id 47387:
id name
47387 Carpet 70x120cm
47373 Mug Mynte Lavender
Now when I look for order items where this product is:
select o.orderid, o.orderdate, i.orderitemsid, p.productid
from orders o, orderitems i, product p
where p.productid = 47387
and p.productid = i.orderitems2productid
and o.orderid = i.orderitems2orderid
It gives me:
orderid orderdate orderitemsid productid
19157 2021-02-08 88304 47387
17600 2020-10-13 81281 47387
14462 2019-12-28 67561 47387
So the initial query somehow gives all products that have been ordered
at least before January 1st 2021
but not only before that date.
cheers
richard
On Wed, 19 Jan 2022 05:04:09 -0700, Rob Sargent wrote:
> On 1/19/22 04:03, Richard Klingler wrote:
>> Good morning (o;
>>
>>
>> I am in the process of migrating an online shop to another system and
>> therefore
>> also want to clean out products that haven't been re-stocked for a time.
>>
>> Now this simple query returns all order ids younger than 750 days:
>>
>> select orderid, orderdate from orders
>> where (now() - orderdate) < INTERVAL '1000 days'
>> order by orderdate asc
>>
>> So it shows me orders beginning from January 1st 2020...all fine.
>>
>>
>> Now I want to list all products which stock is 0 and have only been
>> ordered
>> before those 750 days..so I use the above query in wrap it in the select
>> with a "not in":
>>
>> select p.productid as id, p.name_de as name
>> from product p, orderitems i, orders
>> where p.productid = i.orderitems2productid
>> and i.orderitems2orderid not in (select orderid from orders where
>> (now() - orderdate) < INTERVAL '750 days')
>> and p.pieces < 1
>> and p.active = 't'
>> group by id
>> order by id desc
> something like this?
>
> select p.productid as id, p.name_de as name
> from product p join orderitems i on p.productid = i.orderitems2productid
> join orders o on i.orderid = o.orderid
> where o.orderdate < 'January 1st 2020'
> and p.pieces < 1
> and p.active = 't'
> group by id
> order by id desc
>>
>>
>> Besides that this query takes over 70 seconds...it also returns
>> products that have been ordered after January 1st 2020.
>>
>> So somehow this "not in" doesn't work as I am expecting it (o;
>>
>>
>> thanks in advance
>> richard
>>
>>
>>
>
>
>
view thread (12+ messages) latest in thread
Message-ID: <20220119134059286538.4c015b19@klingler.net>
Permalink: ../20220119134059286538.4c015b19@klingler.net/
Also on: postgresql.org/message-id/20220119134059286538.4c015b19@klingler.net
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: richard@klingler.net, pgsql-sql@lists.postgresql.org
Subject: Re: Clean up shop database
In-Reply-To: <20220119134059286538.4c015b19@klingler.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox