Correct query to return 1 row, instead of duplicates when 2 columns out of many are constant

I have a query with 2 constant columns and another column with different values.

I am having issues with an informix query where for the same id, and name I am getting >1 rows returned because of 3rd select which is causing duplicates.

for example, my query is as below

select
x.id,
x.name
case when (y.tag = 'a') then z.value else '' as A
case when (y.tag = 'b') then z.value else '' as B
case when (y.tag = 'c') then z.value else '' as C
...

From
table x,
join table y on (x.<something> = y.<something>)
join table z on (y.<something> = z.<something>)

Where
x.name = "Test"

The result query returns 3 rows. Each row has a value of either A,B or C. The id and name is always the same and will be.

Basically instead of wanting 1 row returned with all the columns filled, its returning 3 becuase of the different z.value conditions

I want to sorta merge the 3 rows into 1 if that makes sense. There must be something simple or dumb I am missing that could fix this easily. Help please :) Thanks


Apologies, I have added the result of my example query to better explain what its doing verses what I am i wanting:

Result I get

id   123
name qwe
A    z
B
C

id   123
name qwe
A
B    y
C

id   123
name qwe
A
B
C    x

3 Rows retrieved.

Result I want

id   123
name qwe
A    z
B    y
C    x

1 Row retrieved

Hope that clarifies any ambiguity

1 answer

  • answered 2018-11-08 03:11 Gordon Linoff

    I suspect that you simply want aggregation:

    select x.id, x.name
           max(case when (y.tag = 'a') then z.value end) as A,
           max(case when (y.tag = 'b') then z.value end) as B,
           max(case when (y.tag = 'c') then z.value end) as C,
           ...
    from table x join
         table y
         on (x.<something> = y.<something>) join
         table z
         on (y.<something> = z.<something>)
    where x.name = 'Test'
    group by x.name, x.id;