CF query issues

[jonyyg]

hi,

I'm working on an admin page for a department in my school. I have a database of reservations for rooms in which each event has a distinct ID called "confID" Each event or confID often has multiple dates (dateOf).

I need to list out the reservations for the admin page in descending order of date. I have:

<cfquery name="getnums" datasource="lewis">
SELECT DISTINCT confid, dateOf
FROM room
ORDER BY dateOf DESC
</cfquery>

After this I print out all the confid where each confid has its own table that lists all dates.

The problem is that some events have multiple distinct dates (same ConfID but different dateof) so the query I have above creates duplicates of it.

For example, Event A has a confID of 12345. But Event A has dates on Feb 4, 14, 18.
I end up getting 3 tables each listing all 3 dates on each instead of one table listing the 3 dates.

-----
I suppose another way to fix it would be to go through the query removing all duplicate ConfIDs, but I can't seem to find a function or method to do that.
I only knew java coming onto this job so its rather confusing. =P

EDIT:
I tried

<cfquery name="getnums" datasource="lewis">
SELECT DISTINCT confid
FROM room
UNION
SELECT dateOf
FROM room
ORDER BY confID DESC
</cfquery>

but now I get a number of blank tables in my list?

[freshfitz]

How does your table store the dates is each event list separately or does dateof show feb 4,14,18 in the table with 1 event listing?

[jonyyg]

It lists each date as a seperate row.
For example:
Event ID: 1 Date of: 2/15/10 TimeOf: blah
Event ID: 1 Date of 2/16/10 TimeOf: blah
Event ID:2 Date of 3/12/10 TimeOf: blah

So event ID 1 means it is the same event occurring over mulitple days.

[freshfitz]

I had this same problem and I had to use a stored procedure to fix it. I'll see if I can modify it to get it to work with yours.

[samaru]

What kind of data result do you want back from the query? Do you want a resultset that looks like this?

Example:

ConfID	DateOf
----------------------
1	2010-02-15,2010-01-25
2	2010-02-15,2010-03-15,2010-03-15,2010-04-03,2010-05-26
4	2010-06-15,2010-06-25

Are we dealing with just one table? Also, what flavor of SQL are you using? The first query that you showed that has the UNION statement would not be allowed in TSQL because you're unioning columns of different types (assuming confID is INT).