[thelist] MySQL Query problem: unique vals from two fields

[Pringle, Ron]

I'm attempting to write a query that grabs unique Years from two date fields in the same table in a MySQL db (5.0.26 IIRC), preferably as a single column or as an array of results. I have a table of art exhibit start and end dates. I want to find all unique years from the exStartDate and exEndDate fields and use them to populate a dropdown for a search form.

The problem stems from exhibits that might start one year but end the next, such as the first entry below. Querying JUST the start or end dates will miss a year. Below is some sample data showing the table structure as well:

exStartDate	exEndDate
----------------------------------------
2004-12-01	2005-01-20
2005-01-21	2005-03-20
2005-09-16	2005-10-23
2005-10-26	2006-02-06
2005-11-04	2006-01-06
2005-12-09	2006-01-06
2006-01-20	2006-03-19
2006-09-15	2006-11-26
2006-12-08	2007-01-07

Below is the query I've started with, which misses the 2004 date:

SELECT DISTINCT(Year(exEndDate)) as exhibitYear FROM tbl_exhibitDates WHERE exEndDate < CURRENT_DATE() ORDER BY exhibitYear ASC;


Ideas, blows to the head with blunt objects, etc. much appreciated.

Happy Thanksgiving all

Ron