Skip to main content
R
rickclark54
Known Participant
August 18, 2015
Question

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

  • August 18, 2015
  • 1 reply
  • 310 views

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? 

    This topic has been closed for replies.

    1 reply

    H
    haxtbh
    Inspiring
    August 19, 2015

    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?

    R
    rickclark54Author
    Known Participant
    August 19, 2015

    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>

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices