WITH subquery AS (
    SELECT bs.job_statuses_id, bp.product_id
    FROM bestseller_product bp
    LEFT JOIN best_sellers bs ON bs.id = bp.best_seller_id
    UNION ALL
    SELECT jp.job_status_id AS job_statuses_id, jp.product_id
    FROM jobstatus_product jp
)
SELECT DISTINCT p.asin, p.domainId, p.id, js.id, js.created_at,js.priority
FROM products p
JOIN subquery u ON u.product_id = p.id
LEFT JOIN job_statuses js ON u.job_statuses_id = js.id
WHERE p.valid2 = TRUE
ORDER BY 
    CASE
        WHEN js.priority = 'immediate' THEN 0
        WHEN js.priority = 'high' THEN 1
        WHEN js.priority = 'default' THEN 2
        WHEN js.priority = 'low' THEN 3
        ELSE 4
    END,
    js.id ASC,
    p.id ASC
LIMIT 10;