On Thu, Oct 30, 2008 at 11:53:48PM +0100, Thomas wrote:
> Here is the SQL I am working with:
> SELECT products.*, orders.response_code FROM "products" JOIN items ON
> products.id = items.product_id
> LEFT OUTER JOIN orders ON (items.order_id = orders.id AND
> orders.response_code = '0' AND orders
I have found a trick to fool the system: I use an ORDER BY
response_code 0 ASC LIMIT 1
As unpaid orders receive a response_code > 0, then necessarily the
first record has response_code of 0.
However if more and more orders come into the equation, this means
PgSQL will have to process more records
Ok I get the problem. It is the LIMIT 1 which was misleading me.
If I remove this limit, I get many returned results, some where orders
were paid, some where orders were not paid, therefore the LIMIT1 picks
the first one, and by chance it lands on an unpaid order.
Am I trying to achieve something
Here is the SQL I am working with:
--
SELECT products.*, orders.response_code FROM "products" JOIN items ON
products.id = items.product_id
LEFT OUTER JOIN orders ON (items.order_id = orders.id AND
orders.response_code = '0' AND orders.user_id = 2) WHERE (permalink =
E'product-1' AND products.site
For some reason, I now can include the date range search in my ON (...) clause.
However I would like to know if there is a limit to the number of
conditions I can put. It seems that more than 2 conditions misses some
records.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
On Mon, Oct 27, 2008 at 10:47:13AM +0100, Thomas wrote:
> Unfortunately, I cannot use >= in the ON clause when making a search
> on a date range.
The right-hand-side of an ON clause is a general expression; you can
include (and I regularly do) arbitrary functions and operators.
I'm probably missi
Unfortunately, I cannot use >= in the ON clause when making a search
on a date range.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi Tony,
You nailed it! That's the piece of the puzzle I was missing. Moving
the filtering inside the ON clause was the solution.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sat, Oct 25, 2008 at 2:11 PM, Thomas <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I have the following tables:
>
> Product(id, title, price)
> Item(id, product_id, order_id, quantity)
> Order(id, user_id, amount, paid)
> User(id, name)
>
> What I want to achieve is a query on a specific Product based in
Hi,
I have the following tables:
Product(id, title, price)
Item(id, product_id, order_id, quantity)
Order(id, user_id, amount, paid)
User(id, name)
What I want to achieve is a query on a specific Product based in its
title. If the product has at least 1 order in which it appears, then
return the
10 matches
Mail list logo
