CODEWITHSUNDEEP
sqlpostgresql
daniel_mutu
daniel_mutu

Reputation: 163

improve query in PostgreSQL

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

Upvotes: 0

Views: 50

Answers (2)

etsa
etsa

Reputation: 5060

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

Upvotes: 1

Piotr Rogowski
Piotr Rogowski

Reputation: 3880

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

Upvotes: 0

Related Questions