RE: Select Earliest Related Row

Select class_name, 
	GROUP_CONCAT(DISTINCT cl_date
                   		  ORDER BY cl_date DESC SEPARATOR ',  ')
	 (select min(cl_date) from CLASS_DATES where item_id = c.item_id and cl_date > Now())
From CLASSES c
Join CLASS_DATES cd on  (c.item_id = cd.item_id) 
Group by class_name, c. item_id

I did not check it in DB and it can have some parse errors. 

But It should work. 

Best Regards,
Pavel Zimahorau

-----Original Message-----
From: Don Wieland [mailto:[email protected]] 
Sent: Tuesday, February 09, 2016 6:57 PM
To: MySql <[email protected]>
Subject: Select Earliest Related Row

I have a two tables where I am adding CLASSES and CLASS_DATES for people to register for.

Table Name = tl_items (Parent)
item_id
class_name


table_name = tl_items_classes (Children)
class_date_id
item_id
cl_date

“tl_items" will have 0 to Many “tl_items_classes" using the “item_id” as the KEY field.

I am compiling a SELECT query to search the dates of the classes (tl_items_classes rows), but I need to these two things:

1) Only show the EARLIEST “cl_date" in my returned list (i.e... if a class has dates for Jan 2,3,4,5,6 2016, only show Jan 2’s row)

2) In that displayed EARLIEST Class Date row, have a column that displays the complete list of related class dates in ASC order delineated by a COMMA (Group_Concat()???).

Here is a query I have started off with which show all the dates fine. Just want to fine tune it.

SELECT ic.*, i.*, DATE_FORMAT(ic.cl_date, "%M %Y") AS mo_label FROM tl_items_classes ic 
LEFT JOIN tl_items i ON ic.item_id = i.item_id 
WHERE i.active = 1 AND ic.cl_cancelled IS NULL ORDER BY ic.cl_date ASC;

Any help would be appreciated.

Don Wieland
[email protected]
http://www.pointmade.net
https://www.facebook.com/pointmade.band





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql