Where clause conditional on if a type exists

user_8753583 0
Viewed 84

Consider the following query:

SELECT p.*
FROM product p
LEFT JOIN product_time pt ON p.id = pt.product_id
WHERE pt.time BETWEEN ? AND ? 
AND pt.type = 'PRODUCTION_DATE';

I need to change my query so that it will filter first by "RELEASE_DATE". If a production_time with the type "RELEASE_DATE" does not exist, filter by "PRODUCTION_DATE". How would I go about doing this?

where-clause sql
2 Answers

It looks like you want to ensure that either the release date exists -or- or it does not and the production date exists before checking the actual value. For this you one left join for each type:

SELECT product.*
FROM product
LEFT JOIN product_time AS rel_time ON product.id = rel_time.product_id AND rel_time.type = 'RELEASE_DATE'
LEFT JOIN product_time AS prd_time ON product.id = prd_time.product_id AND prd_time.type = 'PRODUCTION_DATE'
WHERE rel_time.pk IS NOT NULL AND rel_time.time BETWEEN ? AND ?
OR    rel_time.pk IS     NULL AND prd_time.time BETWEEN ? AND ?
user_870150

Use LEFT JOIN twice as below:

SELECT p.*
FROM product p
LEFT JOIN product_time pt1 ON p.id = pt1.product_id
    AND pt1.time BETWEEN ? AND ? 
    AND pt1.type = 'RELEASE_DATE'
LEFT JOIN product_time pt ON p.id = pt.product_id
    AND pt.time BETWEEN ? AND ? 
    AND pt.type = 'PRODUCTION_DATE'
WHERE ISNULL(pt1.time, pt.time) IS NOT NULL
user_62228080
Related