0

I have the following tables:

cant:
id      CANT_VAL    COD_VAL fk_id_chest c1    c2
18059   18.56       R12     1           100   11
18060   11          R10     2           200   22
18061   15          R11     3           300   33

col:
FK_ID_COL_DMA   CANT_VAL    COD_VAL
18059           1134        R10
18059           1234        R3
18061           1111        R5

to look like that:

id      CANT_VAL    COD_VAL    fk_id_chest   c1      c2
18059   18.56       R12        1             100     11
18059   1134        R10        1             0       0
18059   1234        R3         1             0       0
18060   11          R10        2             200     22
18061   15          R11        3             300     33
18061   1111        R5         3             0       0

Note that the ID should be common on all rows united by id and c1 - c2 not, because it's not ok for a sum on the column.

If I make an:

    select CANT.ID,CANT.CANT_VAL,CANT.COD_VAL,CANT.FK_ID_CHEST,CANT.C1,CANT.C2 
        from cant
    union
    select COL.FK_ID_COL_DMA,COL.CANT_VAL,COL.COD_VAL,CANT2.FK_ID_CHEST,CANT2.C1,CANT2.C2 
        from col 
            inner join cant2 on cant2.id=COL.FK_ID_COL_DMA

I will get an:

    id      CANT_VAL    COD_VAL    fk_id_chest   c1      c2
    18059   18.56       R12        1             100     11
    18059   1134        R10        1             100     11
    18059   1234        R3         1             100     11
    18060   11          R10        2             200     22
    18061   15          R11        3             300     33
    18061   1111        R5         3             300     33

and if someone would want to know the sum on c1, it will be a bad result! Thanks!

1
  • 3
    You could really help people understanding your questions with clarifications (here, for example, describing why you need a join) and cleaner formatting of code. You could also minimize your examples - here two or three columns behave the same as others, so they are superfluous. Figuring out what you need is currently more like playing puzzle :) Nov 5, 2015 at 13:14

1 Answer 1

0

Ok, I solve it like this:

select CANT.ID,CANT.CANT_VAL,CANT.COD_VAL,CANT.FK_ID_CHEST,CANT.C1,CANT.C2 
        from cant
    union
    select COL.FK_ID_COL_DMA,COL.CANT_VAL,COL.COD_VAL,CANT2.FK_ID_CHEST,0,0 
        from col 
            inner join cant2 on cant2.id=COL.FK_ID_COL_DMA

Thanks!

Not the answer you're looking for? Browse other questions tagged or ask your own question.