2 Replies Latest reply on Aug 19, 2015 5:30 AM by rickclark54

    how do i join two tables in a cfquery, then order by date?

    rickclark54

      I have two tables of events that I want to join in a query, schoolCal and newSchedules. Both have schedID and event. I would like them to order by the eventDate. My code is:

       

      <cfquery name="getevent" Datasource="#application.dsn#">

       

      select schoolCal.eventDate, schoolCal.date_end, schoolCal.event, newSchedules.eventDate, newSchedules.event

      from schoolCal,newSchedules

      where schoolCal.schedID = newSchedules.schedID

      order by eventDate

      </cfquery>

       

      Two problems, the largest problem the newSchedules table is not showing up in the output and I get an error when I try to order by eventDate

      How do I get both tables to show and how do I get the cfquery  to order by eventDate? 

        • 1. Re: how do i join two tables in a cfquery, then order by date?
          haxtbh Level 4

          What are the exact errors you are getting? It will tell you why...

           

          What output are you talking about as you have not shown that above.

           

          You will not be able to sort just by "eventDate" as it is defined twice in the select statement. It is a field in both schoolCal and newSchedules so you will have to define which one of those you want to order by. I.e. order by schoolCal.eventDate

           

          Are you wanting to merge the results of two queries from both these tables or are you looking to join them? Does the schoolCal.schedID actually link to newSchedules.schedID?

          • 2. Re: how do i join two tables in a cfquery, then order by date?
            rickclark54 Level 1

            I'm not really getting an error, the seconed table, newSchedules, doesn't show at all.

            I am trying to merge two different schedules and display the events by the eventDate. If I pick one eventDate over the other, will eventDates from both tables fall in order?

             

            Output:

            <cfoutput query="getevent" maxRows="13">

                   

                    <tr><td align="left" valign="top" width="85">

                   

                        #dateFormat(eventDate, 'mmm dd')#<cfif date_end GT "">&ndash;#dateFormat(date_end, 'dd')#</cfif>

                   

                    </td><td <td>#event#</td></tr>

                    </cfoutput>