CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
user667430
user667430

Reputation: 1547

SQL statement only select items that appear less than twice

I am currently working on a database which stores information which allows users to make reservations at a restaurant.

I am trying create a SQL statement which returns the times which appear less than twice in the reservations table.

At the moment I have this but this only returns the times which do not appear in the reservations table at all.

SELECT *
FROM TIME
WHERE
    TIME NOT IN (
        SELECT reservation.a_time
        FROM 
            RESERVATION
        JOIN TIME ON 
            reservation.a_time = time.time
        WHERE
            reservation.a_date = :the_date
    )
ORDER BY time;

The above statement returns all times which are not in the reservations table. However how would I return all times which appear in the reservations table including those that appear once but not those that appear twice?

Thanks

Upvotes: 0

Views: 1352

Answers (2)

GolezTrol
GolezTrol

Reputation: 116160

select
  *
from
  TIME t
where
  (select 
    count(r.a_time) 
  from 
    RESERVATION r 
  where
    r.a_time = t.time and
    r.a_date = :the_date) < 2

or

select
  t.time /* and maybe other field, which you need to add to Group By as well */
from
  TIME t
  left join RESERVATION r on r.a_time = t.time and r.a_date = :the_date
group by
  t.time
having
  count(t.time) < 2

I prefer the first, because it is cleaner, more clear and can be expanded easier, despite the subselect.

Upvotes: 3

ean5533
ean5533

Reputation: 8994

If the only thing you care about is returning reservation.a_times that appear EXACTLY once, then this will do it:

SELECT reservation.a_time
FROM RESERVATION
GROUP BY reservation.a_time
HAVING COUNT(*) = 1;

This will group all RESERVATION entries by time, and only return the groups with exactly one member.

Upvotes: -1

Related Questions