RE: OT rookie: link table
people.person_id hahahahahhahahhahhhaahaha.ah. sigh. sorry Chris, back to the topic. 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? -Original Message- From: Chris Kavanagh [mailto:[EMAIL PROTECTED] Sent: 04 April 2005 16:56 To: CF-Talk Subject: rookie: link table Dear list, My task management app gets a list of tasks, like so: -- CFQUERY name=get_tasks datasource=taskomatic SELECT tasks.task_id, tasks.name AS taskname, tasks.deadline, tasks.project_id, tasks.importance_id, tasks.owner_id, tasks.lead_time, tasks.status_id, projects.project_id, projects.name AS projectname, projects.colour, projects.company_id, people.person_id, people.name AS ownername, importance.importance_id, importance.importance AS importancename, FROM tasks JOIN projects ON tasks.project_id = projects.project_id JOIN people ON tasks.owner_id = people.person_id JOIN importance ON tasks.importance_id = importance.importance_id WHERE tasks.status_id = 1 CFIF Session.company_id neq 0 !--- this is the bit i need help with--- /CFIF ORDER BY #order# /CFQUERY -- What I'd like to happen is that: if the session variable company_id is not 0, it only gets TASKS that belong to the PROJECT that the PERSON is cleared for. My link table is called PEOPLE_PROJECT and it has two columns: -- person_id | project_id -- There is a tasks.project_id and of course a project.project_id (and a people.person_id if that's relevant). How do I do this? I can't seem to get my head around it. Many thanks! CK. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201378 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT rookie: link table
people.person_id hahahahahhahahhahhhaahaha.ah. 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. 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? Thanks! CK. ~| 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:201382 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
RE: OT rookie: link table
bit disconcerting there was a massive thread recently about singular/plural table names i have a Session.person_id well, if another join doesnt work for you then you could do a subselect: WHERE tasks.status_id = 1 CFIF Session.company_id neq 0 !--- this is the bit i need help with--- AND project_id IN (select project_id from person_project where person_id = #session.person_id#) /CFIF -Original Message- From: Chris Kavanagh [mailto:[EMAIL PROTECTED] Sent: 04 April 2005 17:29 To: CF-Talk Subject: Re: OT rookie: link table people.person_id hahahahahhahahhahhhaahaha.ah. 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. 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? Thanks! CK. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201388 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT rookie: link table
Chris Kavanagh wrote: people.person_id hahahahahhahahhahhhaahaha.ah. 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 FROMtasks TK JOINprojects PJ ON PJ.project_id= TK.project_id JOINpeople PN ON PN.person_id = TK.owner_id JOINimportance IM ON IM.importance_id = TK.importance_id cfif SESSION.company_id neq 0 JOINpeople_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
[SOLVED] Re: OT rookie: link table
Many thanks, guys, that now works perfectly. Kind regards, CK. ~| 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:201401 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
