Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have different ID_TICKETs (in TKT_HEADER table) with multiple ID_OPERATIONs (in TKT_DETAIL table).
I need to join both tables, in order to get the following fields on a single table:
ID_TICKET / ID_MALL / PERIOD / FLAG_PAY / STAY_TIME / FLAG_AMOUNT / AMOUNT (without ID_OPERATION)
So I'd have to SUM the STAY_TIME and the AMOUNT for every ID_TICKET, not having all the detail from ID_OPERATIONs.
TKT_HEADER
ID_TICKET | ID_MALL | PERIOD | FLAG_PAY | STAY_TIME |
---|---|---|---|---|
TK01 | 3 | 201407 | 0 | 137 |
TK02 | 3 | 201408 | 0 | 3028 |
TK03 | 3 | 201508 | 0 | 2948 |
TK04 | 3 | 201511 | 0 | 421 |
TK05 | 3 | 201512 | 0 | 133 |
TK06 | 3 | 201602 | 0 | 287 |
TK07 | 3 | 201604 | 0 | 113 |
TK08 | 5 | 201509 | 0 | 659 |
TKT_DETAIL
ID_TICKET | ID_OPERATION | FLAG_AMOUNT | AMOUNT |
---|---|---|---|
TK01 | OP01 | 0 | 0 |
TK01 | OP02 | 0 | 0 |
TK01 | OP03 | 1 | 10,74 |
TK02 | OP04 | 0 | 0 |
TK02 | OP05 | 1 | 247,93 |
TK02 | OP06 | 1 | 12,4 |
TK03 | OP07 | 1 | 148,76 |
TK03 | OP08 | 1 | 29,75 |
TK03 | OP09 | 1 | 119,01 |
TK04 | OP10 | 1 | 29,75 |
TK04 | OP11 | 1 | 84,3 |
TK04 | OP12 | 1 | 34,71 |
TK05 | OP13 | 0 | 0 |
TK05 | OP14 | 0 | 0 |
TK05 | OP15 | 1 | 34,71 |
TK06 | OP16 | 0 | 0 |
TK06 | OP17 | 0 | 0 |
TK06 | OP18 | 1 | 34,71 |
TK07 | OP19 | 0 | 0 |
TK07 | OP20 | 0 | 0 |
TK07 | OP21 | 0 | 0 |
TK08 | OP22 | 0 | 0 |
TK08 | OP23 | 0 | 0 |
TK08 | OP24 | 0 | 0 |
TK08 | OP25 | 1 | 2,48 |
The thing is when I try to join both tables by ID_TICKET, I get the following (with STAY_TIME value multiple times):
ID_TICKET | ID_MALL | PERIOD | FLAG_PAY | STAY_TIME | ID_OPERATION | FLAG_AMOUNT | AMOUNT |
---|---|---|---|---|---|---|---|
TK01 | 3 | 201407 | 0 | 137 | OP01 | 0 | 0 |
TK01 | 3 | 201407 | 0 | 137 | OP02 | 0 | 0 |
TK01 | 3 | 201407 | 0 | 137 | OP03 | 1 | 10,74 |
TK02 | 3 | 201408 | 0 | 3028 | OP04 | 0 | 0 |
TK02 | 3 | 201408 | 0 | 3028 | OP05 | 1 | 247,93 |
TK02 | 3 | 201408 | 0 | 3028 | OP06 | 1 | 12,4 |
TK03 | 3 | 201508 | 0 | 2948 | OP07 | 1 | 148,76 |
TK03 | 3 | 201508 | 0 | 2948 | OP08 | 1 | 29,75 |
TK03 | 3 | 201508 | 0 | 2948 | OP09 | 1 | 119,01 |
TK04 | 3 | 201511 | 0 | 421 | OP10 | 1 | 29,75 |
TK04 | 3 | 201511 | 0 | 421 | OP11 | 1 | 84,3 |
TK04 | 3 | 201511 | 0 | 421 | OP12 | 1 | 34,71 |
TK05 | 3 | 201512 | 0 | 133 | OP13 | 0 | 0 |
TK05 | 3 | 201512 | 0 | 133 | OP14 | 0 | 0 |
TK05 | 3 | 201512 | 0 | 133 | OP15 | 1 | 34,71 |
TK06 | 3 | 201602 | 0 | 287 | OP16 | 0 | 0 |
TK06 | 3 | 201602 | 0 | 287 | OP17 | 0 | 0 |
TK06 | 3 | 201602 | 0 | 287 | OP18 | 1 | 34,71 |
TK07 | 3 | 201604 | 0 | 113 | OP19 | 0 | 0 |
TK07 | 3 | 201604 | 0 | 113 | OP20 | 0 | 0 |
TK07 | 3 | 201604 | 0 | 113 | OP21 | 0 | 0 |
TK08 | 5 | 201509 | 0 | 659 | OP22 | 0 | 0 |
TK08 | 5 | 201509 | 0 | 659 | OP23 | 0 | 0 |
TK08 | 5 | 201509 | 0 | 659 | OP24 | 0 | 0 |
TK08 | 5 | 201509 | 0 | 659 | OP25 | 1 | 2,48 |
And finally when I summarize the values I have something like this:
ID_TICKET | ID_MALL | PERIOD | FLAG_PAY | FLAG_AMOUNT | STAY_TIME | AMOUNT |
---|---|---|---|---|---|---|
TK01 | 3 | 201407 | 0 | 0 | 274 | 0 |
TK01 | 3 | 201407 | 0 | 1 | 137 | 10,74 |
TK02 | 3 | 201408 | 0 | 0 | 3028 | 0 |
TK02 | 3 | 201408 | 0 | 1 | 6056 | 260,33 |
TK03 | 3 | 201508 | 0 | 1 | 8844 | 297,52 |
TK04 | 3 | 201511 | 0 | 1 | 1263 | 148,76 |
TK05 | 3 | 201512 | 0 | 0 | 266 | 0 |
TK05 | 3 | 201512 | 0 | 1 | 133 | 34,71 |
TK06 | 3 | 201602 | 0 | 0 | 574 | 0 |
TK06 | 3 | 201602 | 0 | 1 | 287 | 34,71 |
TK07 | 3 | 201604 | 0 | 0 | 339 | 0 |
TK08 | 5 | 201509 | 0 | 0 | 1977 | 0 |
TK08 | 5 | 201509 | 0 | 1 | 659 | 2,48 |
Do you know how could I solve this, to have something like the last table but with the right STAY_TIME and AMOUNT for each ID_TICKET?
Thank you!!!
Hi,
you have different ID_operation for every line
that's why it's working with a single STAY_TIME on the last table, because you removed the ID_OPERATION
I don't think it is about joining or not, I think it is about keeping the ID_OPERATION column on the chart or not..