Skip site navigation (1) Skip section navigation (2)

Re: a LEFT JOIN problem

From: Thomas <iamkenzo(at)gmail(dot)com>
To: "Sam Mason" <sam(at)samason(dot)me(dot)uk>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: a LEFT JOIN problem
Date: 2008-10-30 22:53:48
Message-ID: 55da14450810301553y5510208ak1d9b8982251b0733@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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_id = 1) LIMIT 1
--

An order has been paid when its response_code is 0.

One problem I have is that, if the user has not placed an order for
the product, than I still want the product fields to be returned so I
can't put this condition in a WHERE. If a user has paid an order for
that product, I want all fields from Product to be returned and I want
also the response_code to be returned (it will obviously be 0). But in
the current state of the query, even if there exists a paid order for
the current user_id, then the query misses it and therefore never
returns the response_code.

What's wrong with it?

In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2008-10-30 23:00:49
Subject: Re: psql screen size
Previous:From: Gregory StarkDate: 2008-10-30 22:52:22
Subject: Re: psql screen size

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group