CODEWITHSUNDEEP
sqloracle-database
user3178424
user3178424

Reputation: 37

Query table and compare to another row

I have a table called CLOCK it has columns ID:

 NUMBER, TIME_IN: DATE, TIME_OUT: DATE, START_LUNCH: VARCHAR2, and STOP_LUNCH: VARCHAR2.

I want to select the ID where TIME_IN and TIME_OUT are NOT NULL and START_LUNCH = 'Y' and STOP_LUNCH is NULL and that ID does not have a row where CLOCK_IN is not NULL and CLOCK_OUT is null.

I can get it all but the last part where i want to make sure there is not another row with the same id where CLOCK_IN is not NULL and CLOCK_OUT is NULL. Any help is appreciated.

SELECT ID FROM CLOCK
WHERE TIME_IN IS NOT NULL
AND TIME_OUT IS NOT NULL
AND START_LUNCH = '50'
AND START_LUNCH IS NULL

Upvotes: 1

Views: 44

Answers (2)

San
San

Reputation: 4538

Using not exist:

SELECT a.id
  FROM clock a
 WHERE a.time_in IS NOT NULL
   AND a.time_out IS NOT NULL
   AND a.start_lunch = '50'
   AND a.start_lunch IS NULL
   AND NOT EXISTS (SELECT 'X'
                     FROM clock b
                    WHERE b.clock_in IS NOT NULL
                      AND b.clock_out IS NULL
                      AND b.id = a.id);

Upvotes: 0

yieldsfalsehood
yieldsfalsehood

Reputation: 3085

You can use not exists or not in. Here's an example using not in:

SELECT ID FROM CLOCK
WHERE TIME_IN IS NOT NULL
AND TIME_OUT IS NOT NULL
AND START_LUNCH = '50'
AND START_LUNCH IS NULL
AND ID NOT IN (SELECT ID
               FROM CLOCK
               WHERE CLOCK_IN IS NOT NULL
               AND CLOCK_OUT IS NULL)

The subquery picks off all the ID's matching the conditions you want to exclude and the not in takes care of actually excluding them.

Upvotes: 2

Related Questions