Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
SELECT *, AS  name, p.image, p.price,

( SELECT ps.price FROM product_special ps WHERE =
AND < NOW() ORDER BY ps.priority ASC, LIMIT 1) AS special_price,

( SELECT FROM product_special ps WHERE =
AND < NOW() ORDER BY ps.priority ASC, LIMIT 1) AS date

FROM product p LEFT JOIN product_special ps ON ( =

As you can see I'm repeating the same subquery just to get another column out. I'm wondering is there a better way of doing this?

share|improve this question
Are the id, the Primary Keys in both tables? – ypercube Apr 2 '12 at 23:33
The should raise an error. You have not defined pd anywhere. – ypercube Apr 2 '12 at 23:34
Sorry, was a typo. It should be And yes, id is the primary key in both tables – Sparctus Apr 3 '12 at 1:47
Is (, product_special.priority) unique ? – a1ex07 Apr 3 '12 at 1:51 is. I've no problem making product_special.priority unique if that can help. – Sparctus Apr 3 '12 at 13:03

4 Answers 4

I think this is equivalent:

SELECT, p.image, p.price,

       ps.price AS special_price,

FROM product p 
  LEFT JOIN product_special ps 
    ON =
    AND < NOW()
share|improve this answer
I'm wondering if it should it be WHERE < NOW() in the last line... – Sparctus Apr 3 '12 at 13:04
@Sparctus: No, that would turn the join into an INNER JOIN. Not the same as your query. – ypercube Apr 3 '12 at 13:33

Assuming combination, product_special.priority is unique

 SELECT p.*, special_price,special_date
 FROM product p
     SELECT, ps.price as special_price, ps.`date` as special_date
     FROM product_special ps
       SELECT id, MIN(priority) as min_priority 
       FROM product_special
       GROUP BY id
     ) ps2 
     ON ( =
 )a ON (
share|improve this answer

unless you're intending to return the fields as special_price.price and why not alias the names inside the subquery? e.g.

SELECT p.*, AS  name, p.image, p.price, ps.*
FROM product p
      psi.price as special_price, as my_date 
    FROM product_special psi
    WHERE = AND < NOW()
    ORDER BY psi.priority ASC, LIMIT 1
   ) AS ps ON =

Does your query language have a FIRST() aggregate function? Not sure if you could make the PK of product_special a composite between id and priority (both ASC sort) and change the ORDER clause to GROUP BY id, psi.priority

you MIGHT be able to remove the ORDER BY clause entirely and use HAVING MIN(psi.priority)

share|improve this answer

Note that the "cross apply" mechanism from SQL Server would solve this, but it isn't available in PostgreSQL. Basically, it was their solution for how to pass parameters (which tend to be references to columns external to the current table expression) to functions called as table expressions in the FROM clause. But it turned out to be useful for all kinds of situations where you want to avoid another level of subquery nesting or moving things from the FROM clause to the SELECT clause. PostgreSQL made it possible to do this by making kind of an exception -- you can pass parameters like that if the expression is a simple function call but not strictly speaking an embedded SELECT. So

left join highestPriorityProductSpecial( on true

is ok, but not

left join (select * from product_special ps where = order by priority desc limit 1) on true

even though the definition of the function is precisely that.

So, that is in fact a handy solution (in 9.1 at least): make a function to extract your highest priority row by doing the limit inside the function.

But functions have the drawback that the query plan will not show what is going on inside them and I believe it will always choose a nested loop join, even when that might not be best.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.