3 Replies Latest reply on Mar 25, 2007 8:12 PM by The ScareCrow

    inner join select

    craig_mac Level 1
      can someone help me with this query, i am getting errors, i am not sure how to add a select function to the on clause.

      could someone please advise what i am doing wrong

      <CFQUERY datasource="#application.ds#" Name="GetSchH2">
      SELECT *
      FROM SMS_Players_Table

      INNER JOIN appoint_table
      ON ServerSMS <= #CreateODBCDateTime(Now())# OR ServerSMS <> ''

      INNER JOIN SMS_Clubs_Table
      ON appoint_table.LoginID = SMS_Clubs_Table.ClubID AND Country = 'Australia'

      INNER JOIN (SELECT SUM(Cost)as TotalCost FROM SMS_Records) ON SMS_Records.ClubID = SMS_Clubs_Table.ClubID

      WHERE appoint_table.App_ClientID = SMS_Players_Table.PlayerID AND appoint_table.HCODE = 'H2'
      </cfquery>
        • 1. Re: inner join select
          Dan Bracuk Level 5
          At least one of your problems is here:
          INNER JOIN appoint_table
          ON ServerSMS <= #CreateODBCDateTime(Now())# OR ServerSMS <> ''

          The first part suggests ServerSMS is a timestamp or datetime field and the second part suggests that it is text. At least one of those is wrong.
          • 2. Re: inner join select
            BKBK Adobe Community Professional & MVP
            Wouldn't it be simpler to do something like this for suitable columns W, X, Y, Z, etc. ?

            • 3. Re: inner join select
              The ScareCrow Level 1
              While one problem is as Dan said, another is

              INNER JOIN (SELECT SUM(Cost)as TotalCost FROM SMS_Records) ON SMS_Records.ClubID = SMS_Clubs_Table.ClubID


              Should be

              INNER JOIN (SELECT SUM(Cost)as TotalCost, ClubID FROM SMS_Records GROUP BY ClubID) As subTable ON subTable.ClubID = SMS_Clubs_Table.ClubID

              Note that you need to alias the sub select to join to the SMS_Clubs table, and because you need the clubid to join you have to return this in the select clause, thus you then have to group the SUM by the clubid.

              Ken