8 Replies Latest reply on Apr 10, 2007 12:03 AM by JohnGree

    query problem

    JohnGree Level 1
      hi i am trying to query 3 table in my database but i am not getting the right output

      i have tried this query below, i need to display <cfset smsleft = #GetUser.SMSBought# - #GetUser.SMSCost#>

      but the result is a massive number, so i must be getting a conflict somewhere any ideas what i need to do

      <CFQUERY datasource="#application.ds#" Name="GetUser">
      SELECT *
      FROM
      (SELECT
      CT.ClubName, CT.ClubID, CT.ContactEmail, CT.Password,
      SUM(SR.Cost) AS SMSCost, SUM(PTS.SMS_Amount) AS SMSBought

      FROM SMS_Clubs_Table CT

      INNER JOIN SMS_Records SR
      ON CT.ClubID = SR.ClubID

      LEFT OUTER JOIN payment_table PTS
      ON CT.ClubID = PTS.ClubID

      WHERE CT.School = 0
      GROUP BY CT.ClubID
      ) AS BB
      </cfquery>
        • 1. Re: query problem
          jdeline Level 1
          Insert a <CFDUMP VAR=#getUser#> after your query and see what's being returned.
          • 2. Re: query problem
            JohnGree Level 1
            ok thanks, the variable
            SUM(PTS.SMS_Amount) AS SMSBought
            is show massive numbers,

            what would i need to do to change the query?
            • 3. Re: query problem
              Dan Bracuk Level 5
              First, you are selecting everything from a sub-query. Why not just run the subquery as a subquery?

              Secondly, your query should have crashed instead of giving you a big number. You are selecting 4 fields and only grouping by 1 of them.
              • 4. query problem
                JohnGree Level 1
                run a subquery as a subquery? not sure what you mean

                I have tried this but still get the same big numbers

                <CFQUERY datasource="#application.ds#" Name="GetUser">
                SELECT CT.ClubName, CT.ClubID, CT.ContactEmail, CT.Password,
                SUM(SR.Cost) AS SMSCost, SUM(PTS.SMS_Amount) AS SMSBought
                FROM SMS_Clubs_Table CT, SMS_Records SR, payment_table PTS

                WHERE CT.ClubID = SR.ClubID AND CT.School = 0 AND CT.ClubID = PTS.ClubID
                GROUP BY CT.ClubID
                </cfquery>
                • 5. Re: query problem
                  Dan Bracuk Level 5
                  run a subquery as a query, which you just did. You are still grouping by only one field instead of 4.
                  • 6. Re: query problem
                    JohnGree Level 1
                    ok thanks i have tried the code below, still get big numers, what 4 do i need to group by?

                    <CFQUERY datasource="#application.ds#" Name="GetUser">
                    SELECT CT.ClubName, CT.ClubID, CT.ContactEmail, CT.Password,
                    SUM(SR.Cost) AS SMSCost, SUM(PTS.SMS_Amount) AS SMSBought
                    FROM SMS_Clubs_Table CT, SMS_Records SR, payment_table PTS


                    WHERE CT.ClubID = SR.ClubID AND CT.School = 0 AND CT.ClubID = PTS.ClubID
                    GROUP BY PTS.ClubID, CT.ClubID, SR.ClubID
                    </cfquery>
                    • 7. Re: query problem
                      Dan Bracuk Level 5
                      Maybe those big numbers are accurate. Did you look at the data?
                      • 8. Re: query problem
                        JohnGree Level 1
                        yes i have looked at that and the numbers should be around 20 not 233323

                        so must be a conflict somewhere

                        what i need to is the bought variable should be a sum of SMS_Amount group by each user

                        so i can show a cfoutput of

                        ClubName bought