Re: help- SQL problem, search for duplicates not working, boolean problem?

D wrote:

> I'm using Oracle 8i.
>
> I have a problem, I need am writing an app that allows people to
> reserve meeting rooms. Some of these meeting rooms have A+B wings w/
> dividers, so that two small meetings can occur in the room at the same
> time. Everything is great, but my supervisor came back to me and said
> that I needed to create a report that showed all the people who
> registered to have their meetings in one of these split-up meeting
> rooms at the same time a meeting is going on next door.
>
> My initial query to see who has meetings at the same time is:
>
> SELECT EMPLOYEE.EMPLOYEENAME, LOCATION.LOCATIONROOMNUMBER,
> RESERVATION.RESERVATIONDATE
> FROM EMPLOYEE, RESERVATION, LOCATION
> WHERE EMPLOYEE.EMPLOYEEID = RESERATION.EMPLOYEEID AND
> RESERVATION.RESERVATIONID = LOCATION.LOCATIONID AND
> RESERVATION.RESERVATIONDATE IN
> (SELECT R2.RESERVATIONDATE
> FROM RESERVATION R2
> WHERE R2.EMPLOYEEID != RESERVATION.EMPLOYEED)
> ORDER BY EMPLOYEE.EMPLOYEENAME, LOCATION.LOCATIONROOMNUMBER
> ;
>
> That query worked perfectly, I got all the reservations that were made
> on the same day and time. I checked this in the test db by hand to
> make sure none were left out. Then I added this:
> AND RESERVATION.LOCATIONID IN
> (SELECT R3.LOCATIONID
> FROM RESERVATION R3
> WHERE R3.EMPLOYEEID != RESERVATION.ADMISSIONID)
>
> To make sure that it ALSO checked for reservations made for the same
> room (allowable in our split-room situation).
>
> However this query returned crazy results:
>
> SELECT EMPLOYEE.EMPLOYEENAME, LOCATION.LOCATIONROOMNUMBER,
> RESERVATION.RESERVATIONDATE
> FROM EMPLOYEE, RESERVATION, LOCATION
> WHERE EMPLOYEE.EMPLOYEEID = RESERATION.EMPLOYEEID AND
> RESERVATION.RESERVATIONID = LOCATION.LOCATIONID AND
> RESERVATION.RESERVATIONDATE IN
> (SELECT R2.RESERVATIONDATE
> FROM RESERVATION R2
> WHERE R2.EMPLOYEEID != RESERVATION.EMPLOYEEID AND
> RESERVATION.LOCATIONID IN
> SELECT R3.LOCATIONID
> FROM RESERVATION R3
> WHERE R3.EMPLOYEEID != RESERVATION.EMPLOYEEID))
> ORDER BY EMPLOYEE.EMPLOYEENAME, LOCATION.LOCATIONROOMNUMBER
>
> I had hoped the above query would take the results from the duplicate
> days and filter it to only include those with duplicate rooms. How
> can I do that? If I'm way off, please let me know. I presume I just
> have a mind block where the boolean login should be but I just don't
> know. I've tried many different options.
>
> Thanks for any advice!
>
> Don

How about this approach?

SELECT reservationdate, roomnumber, COUNT(*) FROM yourtables
GROUP BY reservaationdate, roomnumber
HAVING COUNT(*) > 1 Then grab the reservationdate and roomnumber from it:

SELECT reservationdate, roomnumber
FROM (
   SELECT reservationdate, roomnumber, COUNT(*)    FROM yourtables
   GROUP BY reservaationdate, roomnumber    HAVING COUNT(*) > 1) And finally use the reservationdate and room number in the WHERE clause of another query to grab whatever information you want from whatever tables you want?

All of which assumes that the roomnumber is the same for both reservations (you don't indicate one way or the other).

Daniel Morgan Received on Wed May 01 2002 - 14:01:50 CDT