improve query in PostgreSQL

Question

I need to improve this query in PostgreSQL

select a.*
            ,(select num
                    from TABLEA c
                 where c.one = a.one
                     and c.two = a.two
                     and c.three = a.three
                     and c.four= a.four
                     and  c.five = 'A') as A
            ,(select num
                    from TABLEA c
                 where c.one = a.one
                     and c.two = a.two
                     and c.three = a.three
                     and c.four = a.four
                     and c.five = 'B') as B
            ,(select num
                    from TABLEA c
                 where c.one = a.one
                     and c.two = a.two
                     and c.three = a.three
                     and c.four = a.four
                     and c.five = 'C') as C
                from TABLEB a

I'd like to run only one select to get A, B, C....

etsa · Accepted Answer · 2017-09-15 13:34:07Z

1

I think you can try this:

SELECT A.*, 
        C.A, C.B, C.C
FROM TABLEB A
LEFT JOIN (
    SELECT one, two, three, four
        , MAX( CASE WHEN five = 'A' THEN num ELSE NULL END) AS A
        , MAX( CASE WHEN five = 'B' THEN num ELSE NULL END) AS B
        , MAX( CASE WHEN five = 'C' THEN num ELSE NULL END) AS C
    FROM TABLEA 
    WHERE five IN ('A','B','C')
    GROUP BY one, two, three, four
) C ON  c.one = a.one
                     and c.two = a.two
                     and c.three = a.three
                     and c.four= a.four;

Output:

    one two three   four    a       b   c
1   1   2   3       4       10      20  30
2   5   6   7       8       NULL    100 NULL

edited Sep 15, 2017 at 13:34

answered Sep 15, 2017 at 13:28

etsa

5,0501 gold badge9 silver badges18 bronze badges

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

1 Comment

daniel_mutu Over a year ago

Your query is incredible! Thanks very much!!

Piotr Rogowski · Accepted Answer · 2017-09-15 13:27:26Z

0

try like this:

select 
(CASE WHEN five = 'A' THEN num ELSE NULL END),
(CASE WHEN five = 'B' THEN num ELSE NULL END),
(CASE WHEN five = 'C' THEN num ELSE NULL END)
from TABLEA

answered Sep 15, 2017 at 13:27

Piotr Rogowski

3,90021 silver badges24 bronze badges

Collectives™ on Stack Overflow

improve query in PostgreSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related