Can't really help without more information... How are you storing consultantid in the tbl_clients table, specifically for when you have added multiples? I suspect what you really need here is a many-to-many relationship for which you will need a linking table in between your current two.
-- Jay On Tue, Apr 15, 2008 at 10:11 AM, Toby King <[EMAIL PROTECTED]> wrote: > Hi there > > I have an application almost complete and I'm stuck on this last part. > > In summary the application has been developed to allocate consultant in a > management consulting company to different client jobs. > > i.e. I can add Consultant A to job 1 (this is being done already without any > troubl). > > I was requested then to allow for more than 1 consultant to be allocated to > a client job (this is also done - I have a drop down list where by a user can > make multiple selections). > > In the client/job file I have a information in this table. I have a > clientid, clientjobid and also staffid fields etc. If I have only added one > staff member everything is OK. Where I have added more than 1 staff members > I am coming unstuck. > > I have a query > > SELECT * > FROM tbl_clients cl, tbl_consultants ct > Where cl.active = 1 AND ct.consultantid = cl.consultantid > > Basically the problem that I have got is that because there is more than one > id in the consultantid field I am not picking up the record. > > Just wondering if there is a better way to do this or what people recommend. > > Thanks in advance. > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303387 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
