Postgresql – Combining two similar queries into one (PostgreSQL)

postgresqlselect

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.

Best Answer

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;

Related Solutions

Mysql – SELECT with large WHERE IN clause taking long time

This is what Joins are for. You don't have to get a list of patients ids and then send them back to the SQL engine. You can combine the two queries into one, with JOIN:

SELECT x.* 
FROM xrays AS x
  JOIN appointments AS a
    ON a.patID = x.patID 
WHERE a.docID IN ('docid1', 'docid2', ..... , 'docidn') 
ORDER BY x.patID                   --- optional so your application gets
                                   --- the data of patients, ordered.

Other things to check is the indexes you have on the tables. As a rule of thumb, at least all columns used in joins, like your docID, patID, should have an index.

MySQL Insert into two tables using new IDs

After your query runs, you'll have a set of copied rows (with configid=41) and an identical set of pasted rows (except for the configid=76 and the auto-created id).

Since, these ids are not known in advance, you'll need another way to identify rows of the config table, e.g. a unique key (besides the auto-incrementing one), so you can match (join) the newly created rows with the old ones.

If, for example, the (configid, optionname) is unique, then the following would work:

INSERT INTO pricing
    ( relid, price, ... )          --- relid and all the other columns, 
                                   --- except any autoincrement you may have
SELECT pasted.id, p.price, ....    --- and the same columns here
FROM 
    pricing AS p
  JOIN
    tblproductconfigoptionssub AS copied
      ON  copied.id = p.relid
      AND copied.configid = 41 
  JOIN
    tblproductconfigoptionssub AS pasted
      ON  pasted.optioname = copied.optioname
      AND pasted.configid = 76 ;

Best Answer

Related Solutions

Mysql – SELECT with large WHERE IN clause taking long time

MySQL Insert into two tables using new IDs

Related Question