He did, it's footnoted at the bottom of his email as a URL and the query is pasted at the bottom too. We tend to miss things at the bottom of emails :P
-- George H [email protected] On Wed, Feb 25, 2009 at 2:15 PM, Rick Hillegas <[email protected]> wrote: > Hi Mikel, > > Could you provide a script which reproduces this problem on Derby? > > Thanks, > -Rick > > Mikel Irurita wrote: >> >> 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 >> >> >> ------------------------------------------------------------------------------------- > >
