Help with SQL Query

[Colin Butler]

I've built a table where each record relates to a meeting. Meetings may be
independent or may be linked indicating that they occur sequentially. I have
a field in the record which allows me to record the record number of the
meeting the current record follows, if the meetings are linked. I want to
search to find all meetings linked to a specific meeting. I can find the
original meeting and its immediate follower but I can't figure out how to
find the second ( or subsequent) meeting out because they do not contain the
original record number in any field. Is there a way to do what I'm trying to
do? It's like my query needs to loop, first finding the record number being
searched for and any immediate dependents and then looping to find record
numbers included in those records found in the first pass. Any help will be
greatly appreciated!

[Aaron Bertrand - MVP]

Can you show your table structure, sample data, and desired results, and
tell us which database and version you are using? You can likely do this
with one query (no looping or nested recordsets), but the syntax might be
different depending on the above... we need more information.




"Colin Butler" <cjbutler@stonehousegroup.com> wrote in message
news:E1gPa.195850$Xl.3284895@twister.rdc-kc.rr.com...

> I've built a table where each record relates to a meeting. Meetings may be
> independent or may be linked indicating that they occur sequentially. I

have

> a field in the record which allows me to record the record number of the
> meeting the current record follows, if the meetings are linked. I want to
> search to find all meetings linked to a specific meeting. I can find the
> original meeting and its immediate follower but I can't figure out how to
> find the second ( or subsequent) meeting out because they do not contain

the

> original record number in any field. Is there a way to do what I'm trying

to

> do? It's like my query needs to loop, first finding the record number

being

> searched for and any immediate dependents and then looping to find record
> numbers included in those records found in the first pass. Any help will

be

> greatly appreciated!
>
>

[kasia_x]

Hello,

Here is my newbie problem in a simplified version:

I am assigning colors to names and storing this data in an Access table with
three columns: ID, Name, Color. Each name can have more than one color assigned
(but there can be only one Name-Color combination), so my table looks like this:

Name Color
John blue
John red
John yellow
Tom blue
Tom green
Mary blue
etc.

How do I write a statement which would output the data listing all colors
assigned to each name in the following format:

John: blue red yellow
Tom: blue yellow
Mary: blue
etc.

When I use:

<cfquery name="myQuery" datasource="myDatasource">
select Name, Color
from myTable
</cfquery>

<cfoutput query="myQuery">
#Name#: #Color#<br>
</cfoutput>

of course I get:

John: blue
John: red
John: yellow
Tom: blue
Tom: green
etc.

I tried to qun a query within a the output of another query, but then I'm
getting errors about nested output tags.

It seems simple, but I can't figure it out.
Thank you.
Kasia

[weswhite7]

when you or they enter in their color, you can just ahve them seperate their colors by commas, thats probably the easiest way

[philh]

NO. Don't do that. It flies in the face of good DB design.

If all you're concerned with is the display format, try this:


<cfquery name="myQuery" datasource="myDatasource">
select Name, Color
from myTable
ORDER BY Name, Color
</cfquery> <!---BTW "Name" is a reserved word in a lot of DBs --->

<cfoutput query="myQuery" GROUP="Name">

#Name#:
<cfoutput>#Color#</cfoutput>
<br>
</cfoutput>

HTH,

[kasia_x]

Thank you, philh. That's exactly what I was looking for.

BTW "Name" is a reserved word in a lot of DBs
Yes, I have found it out the hard way :-)

Thanks again.
Kasia

[Dave914]

Why am I getting an "Invalid CFML construct" error when running the following
query:

<cfquery datasource="#DB#" name="docheck">
SELECT * FROM meetingplaces WHERE room_no=#RoomNum# AND
((start_date_of_meeting BETWEEN #StartDate# AND #EndDate#) OR
(end_date_of_meeting BETWEEN #StartDate# AND #EndDate))
</cfquery>

I want my query to check if the room number is the same as the one that was
passed into the script and also check if the start date of the meeting in the
database is between the start and end dates passed in OR if the end date of the
meeting in the database is between the start and end dates passed into the
script. Thanks in advance.

[mxstu]

You're missing a # sign after "EndDate"

..... AND #EndDate))

Also, try not to use SELECT *. It's less intuitive, requires a bit more work
on the db side and usually returns more information than needed.

[Dave914]

Thanks! It's been a long day. Time to go home!!

[mxstu]

Yes, we've all been there ;-)

I don't know whether this is necessary for you application, but you may also
want to make sure the "startDate" and "endDate" do not span the dates of an
existing record (i.e. Start/EndDate of 08/01/2005 - 08/31/2005 would span a
meeting date of 08/02/2005 to 08/30/2005)

[Dave914]

Thanks for your help. I appreciate it. My application is just for same-day
meetings, so meetings will never span more than 1 day (5 hours at the most).
So, in my database, I capture the start date & time (ie. 8/11/2005 08:30:00)
and the end date & time (i.e. 8/11/2005 09:30:00). So, when a user requests a
new meeting, my thought was to check and see if either the start date & time
that the user requested falls between a start time and end time already found
in the database OR if the end date & time requested by the user falls between a
start time and end time present in the database. Does that make sense? Do you
think my query is correct? Thanks.

[mxstu]

Okay. I suspected it was dealing with times and not just dates, but I think
the issue still applies. If you have a meeting scheduled for 10AM - 2PM and
the requested time is 9AM - 3PM, you would not want to schedule the meeting
because the full time is not available. So, I think you should add this logic
to your query.

I would also be careful with your inserts. Depending on what level of locking
you are using, it may be possible for another user to reserve the meeting time
you just checked, in between your SELECT query and your INSERT, causing
conflicting reservations.

[Dave914]

You are absolutely correct. I overlooked this. How should I modify my SQL
query? I guess I need to check if any time between the 2 times (i.e. 10 a.m. -
2 pm.) is contained in the existing times (i.e. 9AM - 3PM). How would I do
this? Thanks for your help on this.

[Dave914]

I guess I need to modify the SQL statement so that the requested start
date/time is less than the start date/time in the database AND the requested
end date/time is greater than the end date/time in the database. What do you
think?

[mxstu]

That sounds right, but I'm still trying to dredge up the memory of the last reservation system I worked on ;-)

[Dave914]

Thanks. If you remember how, let me know. Thanks again.

[mxstu]

I'll remember it and will let you know.

I hate to dredge up another issue, but you just make sure you are clear on how
BETWEEN operates. It may prevent a reservation like this because the meeting
start is BETWEEN #RequestedStart# and #RequestedEnd#

Requested time: 7AM - 8AM
Existing meeting: 8AM - 10AM

Catch you later.

[Dave914]

You're right! How does this look?

SELECT * FROM meetingplaces WHERE room_no=#RoomNum# AND
(
((start_date > #RequestedStart# AND start_date < #RequestedEnd#) OR (end_date

> #RequestedStart# AND end_date < #RequestedEnd#))

OR
((#RequestedStart# < start_date) AND (#RequestedEnd# > end_date))
)

[mxstu]

Almost, but it doesn't catch the following conditions:

1) The requested times exactly match the times of an existing meeting
2) An existing meeting spans the requested time. For example
Existing meeting: 8AM - 11AM
Requested time: 9AM - 10AM

[mxstu]

Here is the basic logic we used in the last reservation application. It also
functions as a good testing checklist for the final sql query. The attached
query should satisfy all of the conditions below.

1. Requested start/end time cannot match an existing meeting

Requested: 8AM - 11AM
Existing: 8AM - 11AM

2. Requested start cannot intersect existing meeting time

Requested: 9AM - 2PM
Existing: 8AM - 11AM

3. Requested end cannot intersect existing meeting time

Requested: 7AM - 9AM
Existing: 8AM - 11AM

4. Requested start/end cannot fall within existing meeting time

Requested: 9AM - 10AM
Existing: 8AM - 11AM

5. Requested start/end cannot span existing meeting time

Requested: 7AM - 2PM
Existing: 8AM - 11AM

6. Requested end time CAN be adjacent to existing meeting start time

Requested: 7AM - 8AM
Existing: 8AM - 11AM

7. Requested start time CAN be adjacent to existing meeting end time

Requested: 11AM - 2PM
Existing: 8AM - 11AM

8. Requested start/end time CAN be between (2) existing meetings

Requested: 12AM - 2PM
Existing 1: 8AM - 11AM
Existing 2: 3PM - 5PM

9. Requested start/end time CAN be adjacent to (2) existing meetings

Requested: 11AM - 3PM
Existing 1: 8AM - 11AM
Existing 2: 3PM - 5PM



SELECT *
FROM meetingplaces
WHERE room_no = #RoomNum# AND
(
( start_date <= #RequestedStart# AND end_date > #RequestedStart# ) OR
( start_date < #RequestedEnd# AND end_date >= #RequestedEnd# ) OR
( start_date >= #RequestedStart# AND end_date <= #RequestedEnd# )
)