agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Rob Sargent <robjsargent@gmail.com>
To: pgsql-sql@lists.postgresql.org
Subject: Re: Clean up shop database
Date: Wed, 19 Jan 2022 05:04:09 -0700
Message-ID: <9713782b-9c29-2497-be30-6dd1af0d550c@gmail.com> (raw)
In-Reply-To: <20220119120317479683.54287f97@klingler.net>
References: <20220119120317479683.54287f97@klingler.net>

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: <9713782b-9c29-2497-be30-6dd1af0d550c@gmail.com>
Permalink:  ../9713782b-9c29-2497-be30-6dd1af0d550c@gmail.com/
Also on:    postgresql.org/message-id/9713782b-9c29-2497-be30-6dd1af0d550c@gmail.com

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: robjsargent@gmail.com, pgsql-sql@lists.postgresql.org
  Subject: Re: Clean up shop database
  In-Reply-To: <9713782b-9c29-2497-be30-6dd1af0d550c@gmail.com>

* 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