cfquery join issue

Question

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 · Accepted Answer

> 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

Anonymous · Answer

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded