Chris Kavanagh wrote:
>>> people.person_id
>>
>> hahahahahhahahhahhhahhhhahaha.....ahhhhh. sigh.
>>
>> sorry Chris, back to the topic.
>
> <:| uh?
>
> I love it when I'm funny but it's a bit disconcerting when I don't know
> why.
Kerry's just stirring the crap. It's to do with the pluralisation of
table names thread that went on for an inordinate length of time a while
back. Don't bite.
>>>it only gets TASKS that belong to the PROJECT that the PERSON
>>
>>is cleared for
>>
>>Im not sure how you are defining "cleared for", but it should just be a
>>couple more Inner Joins?
>
> Well i have a Session.person_id. I should be able to compare that
> somehow with the table people_project (which is a combination of
> person_id and project_id), and thereby find out which project(_id)(s)
> match which the logged in person(_id), right?
My understanding is this:
<cfquery name="tasks" datasource="taskomatic">
SELECT TK.task_id,
TK.name AS task,
TK.deadline,
TK.owner_id,
TK.lead_time
TK.status_id
PJ.project_id,
PJ.name AS project,
PJ.colour,
PJ.company_id,
TK.owner_id,
PN.name AS owner,
IM.importance_id,
IM.importance
FROM tasks TK
JOIN projects PJ ON PJ.project_id = TK.project_id
JOIN people PN ON PN.person_id = TK.owner_id
JOIN importance IM ON IM.importance_id = TK.importance_id
<cfif SESSION.company_id neq 0>
JOIN people_project PP ON TK.project_id = PP.project_id
</cfif>
WHERE tasks.status_id = 1
<cfif SESSION.company_id neq 0>
AND PP.person_id = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="#SESSION.person_id#">
</cfif>
</cfquery>
Run that and <cfdump> it to check if it's what you're looking for.
Don't be scared by the <cfqueryparam>: it's just for creating prepared
statements, which are safer than just dumping values directly from
variables into the statement, and allow the DBMS to cache the query,
which makes it faster on subsequent runs.
K.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201389
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
