Need A Query Help

[chefhung]

Name | End date |start date |interface number-------------------------------------------------------------------------DM2D 1293714000 995896800 0 -1/00DM2D 1293714000 1106830800 -01/01DM2D 1231074000 1231074000 -02/00DM2D 1239890400 1237813200 -02/00DM2D 1244296800 1241359200 -02/00DM2D 1247407200 1244469600 -02/00DM2D 1293714000 1071061200 -02/01DM2D 1293714000 1011272400 -03/00DM2D 1293714000 1011272400 -03/01DM2D 1293714000 996415200 -04/00DM2D 1293714000 996415200 -04/01DM2D 1293714000 996415200 -04/02DM2D 1293714000 996415200 -04/03DM2D 1293714000 996415200 -04/04DM2D 1293714000 996415200 -04/05DM2D 1293714000 996415200 -04/06DM2D 1293714000 996415200 -04/07DM2D 1293714000 996415200 -05/00DM2D 1293714000 996415200 -05/01DM2D 1293714000 996415200 -05/02DM2D 1293714000 996415200 -05/03DM2D 1293714000 996415200 -05/04DM2D 1293714000 996415200 -05/05DM2D 1293714000 996415200 -05/06DM2D 1230642000 996415200 -05/07DM2D 1293714000 1248876000 -05/07DM2D 1293714000 1011272400 -06/00DM2D 1293714000 1011272400 -06/01DM2D 1227618000 1227445200 -07/00DM2D 1229518800 1228914000 -07/00DM2D 1244383200 1244124000 -07/00DM2D 1264856400 1246802400 -08/00DM2D 1293714000 1126015200 -08/01DM2D 1238421600 1229259600 -08/02DM2D 1264856400 1246802400 -08/02DM2D 1238421600 1229259600 -08/03DM2D 1238421600 1229259600 -08/04DM2D 1243346400 1240754400 -08/04DM2D 1238421600 1232888400 -08/05DM2D NULL NULL -08/06DM2D NULL NULL -08/07DM2D 1293714000 1075208400 -08/08DM2D 1293714000 1075208400 -08/09DM2D 1293714000 1075726800 -08/10DM2D 1293714000 1085666400 -08/11DM2D 1293714000 1085666400 -08/12DM2D 1293714000 1085666400 -08/13DM2D 1293714000 1085666400 -08/14DM2D 1293714000 1092578400 -08/15DM2D 1293714000 1119448800 -08/16DM2D 1293714000 1092578400 -08/17DM2D 1248962400 1235307600 -08/18DM2D 1248962400 1235307600 -08/19DM2D 1248962400 1235307600 -08/20DM2D 1239890400 1237899600 -08/21DM2D 1239890400 1237899600 -08/22DM2D 1247407200 1243519200 -08/22DM2D 1293714000 1100782800 -08/23DM2D 1239890400 1238940000 -08/24DM2D 1293714000 1098885600 -08/25DM2D 1293714000 1124114400 -08/26DM2D 1293714000 1105966800 -08/27DM2D 1293714000 1105966800 -08/28DM2D 1235739600 1232283600 -08/29DM2D NULL NULL -8/30DM2D NULL NULL -08/31DM2D NULL NULL -09/00DM2D 1293714000 1008853200 -10/00DM2D NULL NULL -10/01DM2D NULL NULL -10/02DM2D NULL NULL -10/03DM2D NULL NULL -10/04DM2D NULL NULL -10/05--------------------------------------------------------------------Here is my print out booking data base. There has start date and end date.Here is my query to print out all booking history. ----------------------------------------------------------------------------------------------SELECTschedules.scheduletitle, resources.name, reservations.summary,reservations.end_date, reservations.start_dateFROM schedules LEFT OUTER JOIN resources ON schedules.scheduleid = resources.scheduleid LEFT OUTER JOIN reservations ON resources.machid = reservations.machidWHEREschedules.scheduletitle = 'dm2d'ORDER BYresources.name____________________________________________________List is getting bigger and bigger.What I want to do is:1. print out the booking is newer then today2. and print out all start date = null and end date = nullI got the query like this but not working__________________________________________SELECT schedules.scheduletitle, resources.name, reservations.summary,reservations.end_date, reservations.start_dateFROM schedules LEFT OUTER JOIN resources ON schedules.scheduleid = resources.scheduleid LEFT OUTER JOIN reservations ON resources.machid = reservations.machidWHEREschedules.scheduletitle = 'dm2d'AND reservations.end_date >= 1248286238AND reservations.end_date is NULLORDER BYresources.name;___________________________________________please anyone give me a help.

[justsomeguy]

Which database is this for?

[chefhung]

mysql

[justsomeguy]

That query isn't going to return any results because of this part:WHEREschedules.scheduletitle = 'dm2d'AND reservations.end_date >= 1248286238AND reservations.end_date is NULLthere aren't going to be any rows where the end_date is both greater than a certain number, and null. If it's null, it won't be greater. You might try this instead:WHEREschedules.scheduletitle = 'dm2d'AND (reservations.end_date >= 1248286238OR reservations.end_date IS NULL)

[chefhung]

this mysql script will display all the booking and history.-------------------------------------------SELECTschedules.scheduletitle, resources.name, reservations.summary,reservations.end_date, reservations.start_dateFROMschedules LEFT OUTER JOIN resources ON schedules.scheduleid =resources.scheduleid LEFT OUTER JOIN reservations ON resources.machid =reservations.machidWHEREschedules.scheduletitle = 'dm2d'ORDER BYresources.name----------------------------------------------------------------------------------------------------------------------------05/06 -- 12/30/2010 CMUX Infrastructure, 05/07 -- 12/30/2008 CMUX Infrastructure, 05/07 07/29/2009 12/30/2010 CMUX Infrastructure, 06/00 -- 12/30/2010 CMUX Infrastructure, 06/01 -- 12/30/2010 CMUX Infrastructure, 07/00 11/23/2008 11/25/2008 Project: PS – TSW070607/00 12/10/2008 12/17/2008 --07/00 06/04/2009 06/07/2009 Project: 7.3.0.508/00 07/05/2009 01/30/2010 CR 5.4 08/01 -- 12/30/2010 Infrastructure08/02 12/14/2008 03/30/2009 CRIT08/02 07/05/2009 01/30/2010 CRTesting. (expire 31/1/10)08/03 12/14/2008 03/30/2009 CRSIT08/04 12/14/2008 03/30/2009 CRSIT08/04 04/26/2009 05/26/2009 Configuration Testing08/05 01/25/2009 03/30/2009 CR SIT08/06 -- -- 08/07 -- -- ------------------------------------------------------------------------------------------------------------------------------And this script displays all current booking and any "NULL" time.------------------------------------------------------------------------------------------------------------------------------SELECTschedules.scheduletitle, resources.name, reservations.summary,reservations.end_date, reservations.start_dateFROMschedules LEFT OUTER JOIN resources ON schedules.scheduleid =resources.scheduleid LEFT OUTER JOIN reservations ON resources.machid =reservations.machidWHEREschedules.scheduletitle = 'dm2d'AND (reservations.end_date >= 1248286238OR reservations.end_date is NULL)ORDER BYresources.name;------------------------------------------------------------------------------------------------------------------------------05/06 -- 12/30/2010 Infrastructure05/07 07/29/2009 12/30/2010 Infrastructure06/00 -- 12/30/2010 Infrastructure06/01 -- 12/30/2010 Infrastructure08/00 07/05/2009 01/30/2010 CRIT Scope Testing. (expire 31/1/10)08/01 -- 12/30/2010 Infrastructure, POC08/02 07/05/2009 01/30/2010 CRIT Scope Testing. (expire 31/1/10)08/06 -- -- 08/07 -- -- ----------------------------------------------------------------------------------------------------------------After all I find out is this script also filter out "available" interface, it mean some interface has been expire but available. it there any script in mysql like looping any same interfaces to compare "end date ", and I able to print out All available interfaces?

[chefhung]

SELECT resources.name, reservations.start_date, reservations.end_date, reservations.summaryFROM cwlab.resources INNER JOIN cwlab.reservations ON resources.machid = reservations.machidWHERE resources.scheduleid = "cwe0000003640000"GROUP BY resources.machid UNIONSELECT resources.name, reservations.summary, reservations.end_date, reservations.start_dateFROM schedules LEFT OUTER JOIN resources ON schedules.scheduleid = resources.scheduleid LEFT OUTER JOIN reservations ON resources.machid = reservations.machidWHEREresources.scheduleid = "cwe0000003640000"and reservations.end_date is null-----------------------------------------------------------------------------------------------Hi:this query fix my what i want now, but how can i sort them in order(resources.name)?

[justsomeguy]

You can just add an ORDER BY clause at the end like with other queries.

[chefhung]

SELECT schedules.scheduletitle, resources.name, reservations.summary,MAX(reservations.end_date) as end_date, reservations.start_dateFROM cwlab.schedules LEFT OUTER JOIN cwlab.resources ON schedules.scheduleid = resources.scheduleid LEFT OUTER JOIN cwlab.reservations ON resources.machid = reservations.machidWHERE schedules.scheduletitle = 'dm2d'GROUP BY resources.nameORDER BY resources.name----------------------------------------------------------Hi guys:here is my final result to share. it work very good.thanks every one helping me.