10 Replies Latest reply on Jun 27, 2006 12:26 PM by elDonrico

    cfquery join issue

    elDonrico Level 1
      i have had an issue where i am trying to join these 3 tables and count the records in the subquery. if anyone can provide some help with this,.... thnx.

      SELECT DISTINCT *
      FROM ((stSupportTroubleTicket t
      LEFT JOIN stEmployee e ON e.Emp_ID = t.UsersID)
      LEFT JOIN stTTdiscussion d ON d.ttID = t.ID)
      WHERE t.complete = 0 AND t.project = 0
      ORDER BY t.ID DESC

      i'm trying to COUNT the records in stTTdiscussion.

      (this is a support trouble ticket program that has a comment on ticket section. I am making a digg.com type 15 comments interface and i would like to know the number of comments in that one ticket.)

      MSAccess db, win2K3, MX 6.1
        • 1. Re: cfquery join issue
          Level 7
          Don't quote me on this, but I don't *think* one can have two outer joins in
          one query in Access. You might need to separate your SQL into two separate
          queries (where one query queries the second one).

          Are you married to Access as part of your solution? It really is a bit
          "limited" (politely put). Given the choice I'd use MySQL or MSDE or
          something else which actually has a decent feature set and is still free.

          --
          Adam
          • 2. Re: cfquery join issue
            Ryan_Bergman Level 1
            I dont know if this works in access or not, if you had a real database you could do something like this:

            SELECT DISTINCT *
            ,TTCount = (SELECT COUNT(*) FROM stTTdiscussion d WHERE d.ttID = t.ID)
            FROM stSupportTroubleTicket t
            LEFT JOIN stEmployee e ON (e.Emp_ID = t.UsersID)
            WHERE t.complete = 0 AND t.project = 0
            ORDER BY t.ID DESC
            • 3. Re: cfquery join issue
              elDonrico Level 1
              Adam, the query i have here does work, its just that i cant get the count of the discussion table. i am not an advocate of access, it is, though, what i have to work with on this project. if i get the time to do a rehaul, i will change the backend, but, that would give me a lot of queries to change...
              • 4. Re: cfquery join issue
                elDonrico Level 1
                ryan, thanks for the reply, but, no, it doesnt work in access.
                • 5. Re: cfquery join issue
                  elDonrico Level 1
                  ok, i think i am gettin closer, but, i am getting a syntax error...

                  SELECT COUNT(*) AS TTCount ((FROM stTTdiscussion d
                  LEFT JOIN stSupportTroubleTicket t ON t.ID=d.ttID)
                  LEFT JOIN stEmployee e ON e.Emp_ID = t.UsersID)
                  WHERE t.complete = 0 AND t.project = 0
                  ORDER BY t.ID DESC

                  [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

                  • 6. Re: cfquery join issue
                    paross1 Level 2
                    I am assuming that you want to select columns out of the stTTdiscussion tables, since you were doing a SELECT DISTINCT *, so how about this?

                    SELECT DISTINCT *,
                    (SELECT COUNT(*)
                    FROM stTTdiscussion d1
                    WHERE d1.ttID = d.ttID) AS comment_count
                    FROM stSupportTroubleTicket t
                    LEFT JOIN stEmployee e ON e.Emp_ID = t.UsersID
                    LEFT JOIN stTTdiscussion d ON d.ttID = t.ID
                    WHERE t.complete = 0
                    AND t.project = 0
                    ORDER BY t.ID DESC

                    Phil
                    • 7. Re: cfquery join issue
                      Level 7
                      > Adam, the query i have here does work,

                      Hmmm. Well that's a start. I've emulated your DB locally and it works for
                      me too. It might have been because I was doing a mix of RIGHT and LEFT
                      outer joins when I ran into a problem with Access last time.

                      Anyway, is this what you want:

                      SELECT t.ID, count(d.ttID) as discussionCount
                      FROM ((stSupportTroubleTicket t
                      LEFT JOIN stEmployee e ON e.Emp_ID = t.UsersID)
                      LEFT JOIN stTTdiscussion d ON d.ttID = t.ID)
                      WHERE t.complete = 0 AND t.project = 0
                      GROUP BY t.ID
                      ORDER BY t.ID DESC

                      --
                      Adam
                      • 8. Re: cfquery join issue
                        elDonrico Level 1
                        Adam... this is it! it works for the count, but, now i dont have the other fields of the tables selected. for example how would i select the t.problem field if i do this, i get an error.

                        <cfquery name="rsGetTT" datasource="connMOD">
                        SELECT t.ID, t.problem, count(d.ttID) as discussionCount
                        FROM ((stSupportTroubleTicket t
                        LEFT JOIN stEmployee e ON e.Emp_ID = t.UsersID)
                        LEFT JOIN stTTdiscussion d ON d.ttID = t.ID)
                        WHERE t.complete = 0 AND t.project = 0
                        GROUP BY t.ID
                        ORDER BY t.ID DESC
                        </cfquery>

                        [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'problem' as part of an aggregate function.


                        i am assuming that this will work.
                        SELECT t.ID, t.newdate, t.problem, e.emp_ID,e.first_name,e.last_name, count(d.ttID) as discussionCount
                        FROM ((stSupportTroubleTicket t
                        LEFT JOIN stEmployee e ON e.Emp_ID = t.UsersID)
                        LEFT JOIN stTTdiscussion d ON d.ttID = t.ID)
                        WHERE t.complete = 0 AND t.project = 0
                        GROUP BY t.ID, t.problem, e.emp_ID, t.newdate,e.first_name,e.last_name,e.emp_ID
                        ORDER BY t.ID DESC

                        does anyone see a problem with groupby all these fields?
                        • 9. Re: cfquery join issue
                          paross1 Level 2
                          Why are you grouping by e.emp_ID twice?

                          GROUP BY t.ID, t.problem, e.emp_ID, t.newdate,e.first_name,e.last_name,e.emp_ID

                          Phil
                          • 10. Re: cfquery join issue
                            elDonrico Level 1
                            i have to say, this was completely an honest mistake...

                            but, phil, there is no problem having all those fields in the group by, is there? seems like it is the only way i can get to use those fields... (why is that, shouldnt there be another way???)
                            thnx everyone for your help