2

Here's the case - I have the following query:

SELECT DISTINCT post_id FROM
(
    (
        SELECT DISTINCT rp.post_id, pm.meta_value AS premiere_date
        FROM wp_related_people AS rp
        JOIN wp_posts AS p ON rp.post_id = p.ID
        JOIN wp_postmeta AS pm ON pm.post_id = p.ID
        WHERE
            rp.person_id = 494166 AND
            rp.role = 'director' AND
            pm.meta_key = 'htk_premiere_date'
        )
    UNION
    (
        SELECT DISTINCT rp.post_id, '' AS premiere_date
        FROM wp_related_people AS rp
        JOIN wp_posts AS p ON rp.post_id = p.ID
        JOIN wp_postmeta AS pm ON pm.post_id = p.ID
        WHERE
            rp.person_id = 494166 AND
            rp.role = 'director'
    )
    ORDER BY (premiere_date IS NULL), premiere_date DESC
) AS element_ids

The above query works as intended but, as those two inner SELECTs are fairly similar, I was wondering if there is a way to merge them into a single query?

Here's the background: This is related to a WordPress database, expanded with custom tables. I need to get IDs of elements, sorted by htk_premiere_date meta key value. This key & its value is stored in wp_postmeta - table that has the following columns: meta_id (not important here), post_id, meta_key & meta_value. The point is, I need to sort all IDs in the following pattern:

  1. First, IDs that have this htk_premiere_date key set and it's assigned a date value (so sorted by date).
  2. Second, IDs that does not have the htk_premiere_date meta value set, but the key has its row in the wp_postmeta table.
  3. Third - and this is basically the source of the whole problem - if a post ID does not have a row in wp_postmeta table for htk_premiere_date key, it should be treated as in point 2. - so as if it has the key but no value assigned to it.

Thanks a lot for any help!

0

Your inner query should look like

SELECT DISTINCT 
    rp.post_id, 
    CASE 
        WHEN pm.meta_key = 'htk_premiere_date' THEN pm.meta_value 
        ELSE '' 
    END
    AS premiere_date
        FROM wp_related_people AS rp
        JOIN wp_posts AS p ON rp.post_id = p.ID
        JOIN wp_postmeta AS pm ON pm.post_id = p.ID
        WHERE
            rp.person_id = 494166 AND
            rp.role = 'director'

REFERENCE

| improve this answer | |
  • 1
    Works great, thank you! I only replaced the '' with NULL as '' were giving me SQL warnings. – NoviQ Nov 21 '14 at 15:22
1

in ANSI SQL the DISTINCT is totally pointless here. UNION automatically filters duplicates:

test=# SELECT 1 UNION SELECT 1;
 ?column? 
----------
        1
(1 row)

There is a sharp distinction between UNION and UNION ALL:

test=# SELECT 1 UNION ALL SELECT 1;
 ?column? 
----------
        1
        1
(2 rows)

in your case the second subselect can never have the same rows as the first one (due to the empty column) so UNION ALL is already definitely fine here (saving a pointless duplication filter).

| improve this answer | |
  • Actually, after UNION I get two columns (post_id, premiere_date) so the IDs in the first one are often duplicated. That's why I use SELECT DISTINCT in the outer query. – NoviQ Nov 21 '14 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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