whats the best way of doing this

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.

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 <ptanswell@gmail.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

> SELECT ct.* > FROM tbl_clients cl > INNER JOIN tbl_clientConsultants cc ON cc.clientId = cl.clientId > INNER JOIN tbl_consultants ct ON ct.consultantId = cc.consultantId > Where cl.active = 1 > Woops, that's slightly out, that will get all consultants that are assigned to active clients - but hopefully you get the jist and will be able to mold that to your needs. Dominic -- Blog it up: http://fusion.dominicwatson.co.uk

You need a third table to setup the many to many relationship between clients and consultants. A quick summary of the tables will look like this: tbl_consultant --------------------- PK consultantId ... tbl_client -------------- PK clientId ... tbl_clientConsultants ------------------------------ PK clientConsultantId FK consultantId FK clientId + unique constraint/index on consultantId & clientId Then the SQL to select all of a client's constultants could look like this: SELECT ct.* FROM tbl_clients cl INNER JOIN tbl_clientConsultants cc ON cc.clientId = cl.clientId INNER JOIN tbl_consultants ct ON ct.consultantId = cc.consultantId Where cl.active = 1 HTH Dominic -- Blog it up: http://fusion.dominicwatson.co.uk