Combining two similar queries into one (PostgreSQL)

Question

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.

Answer 1

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;

• Joishi Bodio: heh. Our answers are basically identical now.
• LetMeSOThat4U: Marked your answer over Joishi's answer purely for more readable formatting. ;-)

Answer 2

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..