General discussion
December 19, 2003 at 01:27 AM
e.bollen@live;be

Unable to retrieve unique rows from table

by e.bollen@live;be . Updated 22 years, 7 months ago

Hi,

I am facing the following problem:
I would like to retrieve some rows with following sql:

SELECT DISTINCT d.DATE_DT,
d.TERMINAL_ID,
d.TIME_TM AS HOME_DEP,
a.TIME_TM AS HOME_ARR,
d.CAR_KM AS DEP_KM,
a.CAR_KM AS ARR_KM,
a.CAR_KM – d.CAR_KM AS TOTAL_KM,
a.TIME_TM – d.TIME_TM AS TOTAL_TIME
FROM COLLECT d
INNER JOIN COLLECT a
ON d.TERMINAL_ID = a.TERMINAL_ID
AND d.DATE_DT = a.DATE_DT
AND d.TIME_TM < a.TIME_TM WHERE d.JOB = 50 AND a.JOB = 51 AND d.TERMINAL_ID = 166 ORDER BY d.DATE_DT, d.TERMINAL_ID, d.TIME_TM This sql works fine when there is only one departure per day, but when there are 2 departures a day, this sql does not return 2, but 3 rows, where the 2nd row is the combination of departure time 1 and arrival time 2 Can anyone tell a sollution on how to manage this? Thanks

This discussion is locked

All Comments