Dear,
I have a problem executing this query [1]:
It returns the T.ID value duplicated in P.ID, T.ID y C.ID.
All the values are correct.
Example of the result in Derby:
id1 2 0 2009-02-24 12:43:49.136 id1 id1 NULL NULL 0 1000006 3.0 37.0 id1
189.08
id1 2 0 2009-02-24 12:43:49.136 id1 id1 NULL NULL 1 1000004 4.0 13.0 id1
189.08
If I executed this query in other database for example (MySQL, ...) it
works fine.
Example of the expected result:
id1 2 0 2009-02-24 12:43:49.136 id2 id3 NULL NULL 0 1000006 3.0 37.0 id4
189.08
id1 2 0 2009-02-24 12:43:49.136 id2 id3 NULL NULL 1 1000004 4.0 13.0 id5
189.08
Any suggestions?
Thanks in advance.
Regards,
Mikel Irurita.
[1] http://rafb.net/p/LeTm7b34.html
-------------------------------------------------------------------------------------
SELECT T.ID TI_UUID, T.TICKETID TI_ID, T.TICKETTYPE TI_TYPE, R.DATENEW TI_DATE,
P.ID PEO_ID,
C.ID BP_ID, C.SEARCHKEY BP_SKEY, C.NAME BP_NAME,
L.LINE LINE_ID, L.PRODUCT LINE_PRODUCT, L.UNITS LINE_UNITS, L.PRICE LINE_PRICE,
TAX.ID TAX_ID,
SUM(PAY.TOTAL) PAYMENT_TOTAL
FROM RECEIPTS R JOIN TICKETS T ON R.ID = T.ID JOIN TICKETLINES L ON T.ID =
L.TICKET
JOIN TAXES TAX ON L.TAXID = TAX.ID LEFT OUTER JOIN PEOPLE P ON T.PERSON = P.ID
LEFT OUTER JOIN CUSTOMERS C ON T.CUSTOMER = C.ID JOIN PAYMENTS PAY ON
R.ID=PAY.RECEIPT
WHERE (T.TICKETTYPE = 0 OR T.TICKETTYPE = 1) AND T.STATUS = 0
GROUP BY T.ID, T.TICKETID, T.TICKETTYPE, R.DATENEW,
P.ID,
C.ID , C.SEARCHKEY, C.NAME,
L.LINE, L.PRODUCT, L.UNITS, L.PRICE,
TAX.ID
-------------------------------------------------------------------------------------
