Select Distinct with CASE - PostgreSQL

Question

I am trying to select places infos from different tables: I have the place in one table, the geographical coordinates on another, and telephone and email in another. The issue is that we may or not have the email and telephone. I am looking for a way to get the email / phone when they exist, and have a blank value if they are not known. So I use the "CASE" instruction on the SELECT.

If we have no email and no phone, everything goes ok : we have only one line returned.

But if we have the email or the phone, it returns 2 lines; and the worst case is when we have both email and phones: in this case it returns three lines for each place :

place name , blank phone , blank mail
place name , blank phone , populated mail
place name , populated phone , blank mail

I am looking for a way to get only one line :

0) place name + populated (or not) phone + populated (or not) mail

I tried the 'distinct' but it doesn't do the trick :(

Here is my query :

select distinct
    places.name     as place,

    CASE place_properties.pkey      WHEN    'gen.telephone' 
                                    THEN    place_properties.pvalue
                                    END 
                                    as  telephone,

    CASE place_properties.pkey      WHEN    'gen.email' 
                                    THEN    place_properties.pvalue
                                    END 
                                    as  email
from     
    places
inner join
    place_properties   on place_properties.place_id     = places.id

And here is a simplified answer example :

"ALLIAT"                ;"0561059858"   ;""
"ALLIAT"                ;""             ;"[email protected]"
"ALLIAT"                ;""             ;""
"TARASCON SUR ARIEGE"   ;"0561023281"   ;""
"TARASCON SUR ARIEGE"   ;""             ;"[email protected]"
"TARASCON SUR ARIEGE"   ;""             ;""
"Les Cabanes"           ;""             ;""

We see that 'ALLIAT' and 'Tarascon' are returned three time because it has both a phone number and an email while "Les cabanes" is returned only once because it doesn't have any of it.

How to change the SQL to have only one line when we habe the email and/or the phone on the database?

Paul Maxwell · Accepted Answer · 2014-10-23 11:12:01Z

1

try this:

SELECT
      places.name AS place
    , pp.telephone
    , pp.email
FROM places
      LEFT JOIN (
                  SELECT
                        place_id
                      , MAX(CASE place_properties.pkey WHEN 'gen.telephone' THEN place_properties.pvalue END) AS telephone
                      , MAX(CASE place_properties.pkey WHEN 'gen.email' THEN place_properties.pvalue END)     AS email
                  FROM place_properties
                  WHERE place_properties.pkey IN ('gen.telephone', 'gen.email')
                  GROUP BY
                        place_id
            ) AS pp
                  ON places.id = pp.place_id

It might be possible to use alternatives to this approach but for that I would suggest we need to a good set of sample data.

It isn't clear if you only want places that have a phone of email, so I have used a LEFT JOIN which allows all places to be listed. If you do only want those places with (phone or email) then use an INNER JOIN instead

edited Oct 23, 2014 at 11:12

answered Oct 23, 2014 at 10:53

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Romain Jouin Over a year ago

I am willing to have all the places, with the email and phone when they exist; I wil try your idea, thanks

Romain Jouin Over a year ago

as a first test, it returns only blanks mails and telephone

Paul Maxwell Over a year ago

Sorry, add "gen." in from to "telephone" and "email" I mistook this as an alias, but it isn't. The answer has been amended.

Mabu Kloesen · Accepted Answer · 2014-10-23 18:26:37Z

I think you can try this for get all email and telephone number

SELECT places.name AS place,
       pp.telephone
       pp.email
  FROM places
  LEFT JOIN (
                  SELECT
                        place_id, 
                        ARRAY_TO_STRING(ARRAY_AGG(DISTINCT NULLIF(CASE place_properties.pkey WHEN 'gen.telephone' THEN place_properties.pvalue END,'')),',') AS telephone,
                        ARRAY_TO_STRING(ARRAY_AGG(DISTINCT NULLIF(CASE place_properties.pkey WHEN 'gen.email' THEN place_properties.pvalue END,'')),',') AS email
                  FROM place_properties
                  WHERE place_properties.pkey IN ('gen.telephone', 'gen.email')
                  GROUP BY 1
            ) AS pp
                  ON places.id = pp.place_id

Ispirer SQLWays Migrations · Accepted Answer · 2014-10-23 11:00:11Z

0

You can use sub-selects here, like: select name as place,

(select pvalue from place_properties where pkey = 'gen.telephone' and place_properties.place_id     = places.id)                          as  telephone,

(select pvalue from place_properties where pkey = 'gen.email' and place_properties.place_id     = places.id)                          as  email

from
places

answered Oct 23, 2014 at 11:00

Ispirer SQLWays Migrations

1,0589 silver badges16 bronze badges

Collectives™ on Stack Overflow

Select Distinct with CASE - PostgreSQL

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related