0

I have 2 queries like this:

SELECT c.id FROM component c, base_vee bv
    WHERE
        c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199) 
        AND
        c.id = bv.component_id
        AND 
        (bv.affidavit IS NULL OR bv.affidavit = '')


SELECT c.id FROM component c, base_vee bv
    WHERE
        c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199) 
        AND
        c.id = bv.component_id
        AND 
        (bv.affidavit IS NOT NULL AND bv.affidavit != '')

What I need to get is a list of component IDs with False if bv.affidavit is emptyish or True if it's not emptyish.

Right now I'm obviously getting two lists of component IDs for each case.

It works, but one query like described above would be nicer.

3

Use the CASE function:

SELECT c.id,
       CASE WHEN COALESCE(bv.affidavit, '') = '' THEN False
            ELSE True
       END
FROM   component c, base_vee bv
WHERE  c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199) 
AND    c.id = bv.component_id;
| improve this answer | |
  • heh. Our answers are basically identical now. – Joishi Bodio Nov 25 '15 at 17:58
  • Marked your answer over Joishi's answer purely for more readable formatting. ;-) – LetMeSOThat4U Nov 25 '15 at 19:00
1

COALESCE will let you get away with a lot of interesting things when you're dealing with the situation of "if it's null do one thing, otherwise do something else"..

SELECT
  c.id, CASE WHEN COALESCE(bv.affidavit, '') = '' THEN FALSE ELSE TRUE END AS emptyish
FROM component c
JOIN base_vee bv ON c.id = bv.component_id
WHERE c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199)

COALESCE takes the first not-null argument to use, so if bv.affidavit happens to be NULL it will use '' instead.. I shouldn't need to test for it being NOT NULL in the follow up..

| improve this answer | |

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.