cfquery join issue

[elDonrico]

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

[Newsgroup_User]

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

[elDonrico]

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...

[Newsgroup_User · Accepted solution]

> 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

[elDonrico]

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?

[paross1]

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

[elDonrico]

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

[Ryan_Bergman]

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

[elDonrico]

ryan, thanks for the reply, but, no, it doesnt work in access.

[elDonrico]

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.

[paross1]

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