0

I have 2 tables (Oracle database):

ANEXA2A_TABEL1:

id  fk_id_anexa pet1    pet2    alt1    alt2
1   123         10      1       20      2
2   321         11      2       10      3

ANEXA2A_TABEL2_ROWS:

id  fk_id_anexa tip cant
1   123         PET 100
2   123         PET 10
3   123         ALT 200
4   123         ALT 20
5   321         PET 11
6   321         ALT 22

And I need to create a select (used after that in a view) to have a column like in that example:

id  fk_id_a pet1    pet2    pet3    alt1    alt2    alt3
1   123     10      1       110     20      2       220
2   321     11      2       11      10      3       22

I tried:

SELECT a2a.ID, a2a.FK_ID_ANEXA, a2a.PET1, a2a.PET2, a2a.ALT1, a2a.ALT2,
    (SELECT SUM(CANT) FROM ANEXA2A_TABEL2_ROWS where TIP = 'PET') AS PET3,
    (SELECT SUM(CANT) FROM ANEXA2A_TABEL2_ROWS where TIP = 'ALT') AS ALT3
FROM ANEXA2A_TABEL1 a2a
    INNER JOIN ANEXA2A_TABEL2_ROWS a2a2
        ON a2a2.FK_ID_ANEXA = a2a.FK_ID_ANEXA

but I get something like:

id  fk_id_a pet1    pet2    pet3    alt1    alt2    alt3
1   123     10      1       110     20      2       220
1   123     10      1       110     20      2       220
1   123     10      1       110     20      2       220
1   123     10      1       110     20      2       220
2   321     11      2       11      10      3       22
2   321     11      2       11      10      3       22

how can I get just that?

id  fk_id_a pet1    pet2    pet3    alt1    alt2    alt3
1   123     10      1       110     20      2       220
2   321     11      2       11      10      3       22

I guess I have to use a Group By - but I don't know how. Thanks!

  • Your subqueries are not correlated. I doubt they would show what you claim. – ypercubeᵀᴹ Apr 22 '15 at 12:00
  • yah, I don't know how to link fk_id_anexa from the second table with the current row - fk_id_anexa – WDrgn Apr 22 '15 at 12:13
  • now it shows me the total value – WDrgn Apr 22 '15 at 12:37
3

There are several ways to do this. Either remove the join and convert the subqueries to correlated:

SELECT a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2,
    (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS b
     WHERE b.TIP = 'PET' AND b.FK_ID_ANEXA = a.FK_ID_ANEXA
    ) AS PET3,
    (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS  b
     WHERE b.TIP = 'ALT' AND b.FK_ID_ANEXA = a.FK_ID_ANEXA
    ) AS ALT3
FROM ANEXA2A_TABEL1  a ;

or keep the join and use GROUP BY (I converted to LEFT join so you also see results for values that the second table has no related rows):

SELECT a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2,
    SUM(CASE WHEN b.TIP = 'PET' THEN b.CANT ELSE 0 END) AS PET3,
    SUM(CASE WHEN b.TIP = 'ALT' THEN b.CANT ELSE 0 END) AS ALT3
FROM ANEXA2A_TABEL1  a
    LEFT JOIN ANEXA2A_TABEL2_ROWS  b
        ON b.FK_ID_ANEXA = a.FK_ID_ANEXA 
GROUP BY 
    a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2 ;

There are several more options to do this (i.e. first group by table b in a derived table and the join to table a, or using PIVOT, or using the specific to Oracle function DECODE instead of CASE, ...)

0

ypercube is correct in pointing that the output which you claim would be different than the actual output. As the subquery is going to display the total value unless add another filter condition on fk_id_anexa.

    SELECT a2a.ID, a2a.fk_id_anexa, a2a.PET1, a2a.PET2, a2a.ALT1, a2a.ALT2,
(SELECT SUM(CANT) FROM ANEXA2A_TABEL2_ROWS tab2 where TIP = 'PET' and tab2.fk_id_anexa=a2a.fk_id_anexa) AS PET3,
(SELECT SUM(CANT) FROM ANEXA2A_TABEL2_ROWS tab2 where TIP = 'ALT' and tab2.fk_id_anexa=a2a.fk_id_anexa) AS ALT3 FROM ANEXA2A_TABEL1 a2a;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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