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

[Ken Moore]

Hi all,

The problem is that you want to condense 2 fields with different data into 
one field. You would have the same thing if you were searching “First_Name” 
and “Last_Name” while attempting to put them into one field, “All_Names”. 
Some data would be unique to one or the other while other data, i.e. 
“Morgan”,  could be in either, both or neither.

I would suggest a short script that creates a temporary table which 
populates the drop-down list.

An alternative method, using only queries is the following. One query 
returns all years from one field into a temp. table. A second query does the 
same into the same table. A third does a select DISTINCT into a second temp. 
table which is used for the list. This is a little more award. But unless 
you have 100,000’s of records, it should work.

Ken


>From: "Pringle, Ron" wrote

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

_________________________________________________________________
All-in-one security and maintenance for your PC.  Get a free 90-day trial! 
http://clk.atdmt.com/MSN/go/msnnkwlo0050000002msn/direct/01/?href=http://clk.atdmt.com/MSN/go/msnnkwlo0050000001msn/direct/01/?href=http://www.windowsonecare.com/?sc_cid=msn_hotmail