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
<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?