Where clause conditional on if a type exists-CodePudding

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 instead of only filtering by 'PRODUCTION_DATE', 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?

CodePudding user response：

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 ?

CodePudding user response：

Use LEFT JOIN twice as below:

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