Tell me more ×
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.
up vote 4 down vote favorite
1
 
SELECT *, p.name AS  name, p.image, p.price,

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

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

FROM product p LEFT JOIN product_special ps ON (p.id = ps.id)

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
1  
The pd.name should raise an error. You have not defined pd anywhere. – ypercube Apr 2 '12 at 23:34
Sorry, pd.name was a typo. It should be p.name. And yes, id is the primary key in both tables – Sparctus Apr 3 '12 at 1:47
Is (product_special.id, product_special.priority) unique ? – a1ex07 Apr 3 '12 at 1:51
product_special.id is. I've no problem making product_special.priority unique if that can help. – Sparctus Apr 3 '12 at 13:03

2 Answers

up vote 3 down vote

I think this is equivalent:

SELECT p.name, p.image, p.price,

       ps.price AS special_price,

       ps.date

FROM product p 
  LEFT JOIN product_special ps 
    ON  p.id = ps.id
    AND ps.date < NOW()
share|improve this answer
I'm wondering if it should it be WHERE ps.date < 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
up vote 2 down vote

Assuming combination product_special.id, product_special.priority is unique 

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

Your Answer

 
discard

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.