Conditional Statement in PostgreSQL Query

Question

I have here 3 tables namely:

person (
  id int PRIMARY KEY
 ,fullname text)

phonenumber (
  id int PRIMARY KEY
 ,personid int REFERENCES person(id)
 ,phonetypeid REFERENCES phonetype(id)
 ,number text)

phonetype (
 id int PRIMARY KEY
,phonetype text) -- phonetype  'Home', 'Cell', 'Fax', 'Main' etc.

Each person can have multiple numbers stored in the phonenumber table and have different phonetype in the phonetype table:

| fullname         | number        | phonetype |
| Erwin Macale     | (671)632-3909 | Home      |
| Erwin Macale     | (671)632-3909 | Cell      |
| Erwin Macale     | (671)632-3909 | Main      |

I want only to display the phone number of each person with these conditions:

If he has a 'Home' phonetype number display only the home type drop all his other phonetype numbers.
If he has no 'Home' number, then display only his 'Cell' number then drop all his other('Fax', 'Main', etc..) numbers.
A person can have no phonenumber.

I created a temporary table which contains fullname, number, phonetype named testable then from here I separated all phonetype numbers like:

CREATE TEMP TABLE home AS SELECT * FROM testable WHERE phonetype ILIKE 'home';
CREATE TEMP TABLE cell AS SELECT * FROM testable WHERE phonetype ilike 'cell';
CREATE TEMP TABLE main AS SELECT * FROM testable WHERE phonetype ilike 'main';
CREATE TEMP TABLE fax AS SELECT * FROM testable WHERE phonetype ilike 'fax';
CREATE TEMP TABLE work AS SELECT * FROM testable WHERE phonetype ilike 'work';
CREATE TEMP TABLE neighbor AS SELECT * FROM testable WHERE phonetype ilike 'neighbor';
CREATE TEMP TABLE other AS SELECT * FROM testable WHERE phonetype ilike 'other';
CREATE TEMP TABLE unknown AS SELECT * FROM testable WHERE phonetype ilike 'unknown';

and then create another set of temp tables that I think satisfy the above conditions:

CREATE TEMP TABLE all_cell AS SELECT * FROM cell EXCEPT SELECT * FROM home;
CREATE TEMP TABLE all_main AS (SELECT * FROM main) EXCEPT (SELECT * FROM home UNION SELECT * FROM cell);
CREATE TEMP TABLE all_fax AS (SELECT * FROM fax) EXCEPT (SELECT * FROM home UNION SELECT * FROM cell UNION SELECT * FROM main);
CREATE TEMP TABLE all_work AS (SELECT * FROM work) EXCEPT (SELECT * FROM home UNION SELECT * FROM cell UNION SELECT * FROM main UNION SELECT * FROM fax);
CREATE TEMP TABLE all_neighbor AS (SELECT * FROM neighbor) EXCEPT (SELECT * FROM home UNION SELECT * FROM cell UNION SELECT * FROM main UNION SELECT * FROM fax UNION SELECT * FROM work);
CREATE TEMP TABLE all_other AS (SELECT * FROM other) EXCEPT (SELECT * FROM home UNION SELECT * FROM cell UNION SELECT * FROM main UNION SELECT * FROM fax UNION SELECT * FROM work UNION SELECT * FROM neighbor);
CREATE TEMP TABLE all_unknown AS (SELECT * FROM unknown) EXCEPT (SELECT * FROM home UNION SELECT * FROM cell UNION SELECT * FROM main UNION SELECT * FROM fax UNION SELECT * FROM work UNION SELECT * FROM neighbor UNION SELECT * FROM other);

Finally selecting all the unions of the last set of temp tables:

SELECT fullname, number, phonetype FROM (
    SELECT * FROM home
    UNION 
    SELECT * FROM all_cell
    UNION 
    SELECT * FROM all_main
    UNION 
    SELECT * FROM all_fax
    UNION 
    SELECT * FROM all_work
    UNION 
    SELECT * FROM all_neighbor
    UNION 
    SELECT * FROM all_other
    UNION 
    SELECT * FROM all_unknown
) AS t1
ORDER BY t1.fullname, t1.phonetype;

Are the steps to satisfy my conditions right? I'm still getting different phonetype values from one person.

score 3 · Accepted Answer · edited May 23 '17 at 10:31

You went to a lot of trouble to get what this single query can give you:

SELECT DISTINCT ON (p.fullname, p.id)
       p.fullname, n.number, t.phonetype
FROM   phonenumber n 
JOIN   person      p ON p.id = n.personid
JOIN   phonetype   t ON t.id = n.phonetypeid
LEFT   JOIN (
   VALUES
     (1, 'home')
    ,(2, 'cell')
    ,(3, 'main')
    ,(4, 'fax')
    ,(5, 'work')
    ,(6, 'neighbor')
    ,(7, 'other')
    ,(8, 'unknown')
   ) r(rnk, phonetype) USING (phonetype)
ORDER  BY p.fullname, p.id, r.rnk;

DISTINCT ON (p.fullname, p.id) because fullname is probably not unique. I use it anyway to get the sort order you seem to be looking for in a single query level.

Details about DISTINCT ON in this related answer:
Select first row in each GROUP BY group?

I appended the ranking (r.rnk) information with a VALUES expression to make it work with your presented table layout. Better, though, you add to the table phonetype permanently:

ALTER TABLE phonetype ADD COLUMN rnk int;

UPDATE phonetype t
SET    rnk = r.rnk
FROM  (
       VALUES
         (1, 'home')
        ,(2, 'cell')
        ,(3, 'main')
        ,(4, 'fax')
        ,(5, 'work')
        ,(6, 'neighbor')
        ,(7, 'other')
        ,(8, 'unknown')
       ) r(rnk, phonetype)
WHERE   r.phonetype = t.phonetype;

Then your query becomes even simpler:

SELECT DISTINCT ON (p.fullname, p.id)
       p.fullname, n.number, t.phonetype
FROM   phonenumber n 
JOIN   person      p ON p.id = n.personid
JOIN   phonetype   t ON t.id = n.phonetypeid
ORDER  BY p.fullname, p.id, t.rnk    -- add more columns to break ties (if any)

Conditional Statement in PostgreSQL Query

1 Answers1