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

From: D <bbcrock_at_hotmail.com>
Date: 29 Apr 2002 08:26:02 -0700
Message-ID: <22e171df.0204290726.7b0715a9@posting.google.com>

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 Received on Mon Apr 29 2002 - 10:26:02 CDT