sqlpostgresqlouter-joinfull-outer-join

pgSQL FULL OUTER JOIN 'WHERE' Condition

I am trying to create a single query to retrieve the current price and special sale price if a sale is running; When there isn't a sale on I want store_picture_monthly_price_special.price AS special_price to return as null.

Before adding the 2nd WHERE condition the query executes as I expect it to: store_picture_monthly_price_special.price returns null since there is no sale running at present.

 store_picture_monthly_reference | tenure |   name    | regular_price | special_price 
                               3 |     12 | 12 Months |        299.99 | {Null}             
                               2 |      3 | 3 Months  |         79.99 | {Null}            
                               1 |      1 | 1 Month   |         29.99 | {Null}

pgSQL is treating the 2nd WHERE condition as "all or none". If there is no sale running there are no results.

Is it possible to tweak this query so I get regular pricing each and every time and special sale price either as a dollar value when a special is running or returning null Is what I am trying to do be accomplished require sub-query?

This is the query how I presently have it:

SELECT store_picture_monthly.reference AS store_picture_monthly_reference , store_picture_monthly.tenure , store_picture_monthly.name , store_picture_monthly_price_regular.price AS regular_price , store_picture_monthly_price_special.price AS special_price

FROM ( store_picture_monthly INNER JOIN store_picture_monthly_price_regular ON store_picture_monthly_price_regular.store_picture_monthly_reference = store_picture_monthly.reference )

FULL OUTER JOIN store_picture_monthly_price_special ON store_picture_monthly.reference = store_picture_monthly_price_special.store_picture_monthly_reference

WHERE 
    ( store_picture_monthly_price_regular.effective_date < NOW() ) 
    AND
    ( NOW() BETWEEN store_picture_monthly_price_special.begin_date AND store_picture_monthly_price_special.end_date )

GROUP BY store_picture_monthly.reference , store_picture_monthly_price_regular.price , store_picture_monthly_price_regular.effective_date , store_picture_monthly_price_special.price

ORDER BY store_picture_monthly_price_regular.effective_date DESC

Table "store_picture_monthly"

reference bigint,
name text,
description text,
tenure bigint,
available_date timestamp with time zone,
available_membership_reference bigint

Table store_picture_monthly_price_regular

reference bigint ,
store_picture_monthly_reference bigint,
effective_date timestamp with time zone,
price numeric(10,2),
membership_reference bigint

Table store_picture_monthly_price_special

reference bigint,
store_picture_monthly_reference bigint,
begin_date timestamp with time zone,
end_date timestamp with time zone,
price numeric(10,2),
created_date timestamp with time zone DEFAULT now(),
membership_reference bigint
Solution

  • The description of the problem suggests that you want a LEFT JOIN, not a FULL JOIN. FULL JOINs are quite rare, particularly in databases with well defined foreign key relationships.

    In your case, the WHERE clause is turning your FULL JOIN into a LEFT JOIN anyway, because the WHERE clause requires valid values from the first table.

    SELECT spm.reference AS store_picture_monthly_reference, 
       spm.tenure, spm.name, 
       spmpr.price AS regular_price,
       spmps.price AS special_price
FROM store_picture_monthly spm INNER JOIN
     store_picture_monthly_price_regularspmpr
     ON spmpr.store_picture_monthly_reference = spm.reference LEFT JOIN
     store_picture_monthly_price_special spmps 
     ON spm.reference = spmps.store_picture_monthly_reference AND
        NOW() BETWEEN spmps.begin_date AND spmps.end_date
WHERE spmpr.effective_date < NOW();

    Notes: