Can't you just shove an if in there along the lines of, if data exists for client and employer, select only client?
----- Original Message ----- From: "Tim Blair" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, September 12, 2002 2:56 PM Subject: [ cf-dev ] More SQL Q's... > Well, just the one for now anyway... > > I have a list of services, clients and employees. Each client is linked > to a number of services (via a link table, in which the client and > service IDs are specified, along with a cost to that client). The same > is true for an employee. > > I want to SELECT a list of services available to a particular employee. > An employee is linked to a client, so any service which is available to > the client is available to the employee. That's no problem, nor is > selecting the services directly linked to an employee. Now comes the > tricky bit. If a service is lited under the client AND employee, I want > to use the cost stored in the employee table. > > So if we have these under the client: > > Service number one : 123.45 > Service number two : 1000.00 > Service number three : 9.99 > > And these under the employee: > > Service number two : 1111.11 > Service number four : 19.95 > > I want the get the following info back: > > Service number one : 123.45 > Service number two : 1111.11 > Service number three : 9.99 > Service number four : 19.95 > > I've been playing around with UNION (not best I know) to grab all of the > client and employee services and stick them together, but I'm stumped on > the next bit. Not using UNION is a start I guess... ;o) > > Anyone? > > Cheers, > > Tim. > > > ------------------------------------------------------- > Tim Blair > Web Application Engineer, Rawnet Limited > Direct Phone : +44 (0) 1344 393 441 > Switchboard : +44 (0) 1344 393 040 > ------------------------------------------------------- > rawnet ltd > Atrium Court > Bracknell [EMAIL PROTECTED] > Berkshire Tel : +44 (0) 1344 393 040 > RG12 1BW, UK http://www.rawnet.com > ------------------------------------------------------- > This message may contain information which is legally > privileged and/or confidential. If you are not the > intended recipient, you are hereby notified that any > unauthorised disclosure, copying, distribution or use > of this information is strictly prohibited. Such > notification notwithstanding, any comments, opinions, > information or conclusions expressed in this message > are those of the originator, not of rawnet limited, > unless otherwise explicitly and independently indicated > by an authorised representative of rawnet limited. > ------------------------------------------------------- > > > > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > For human help, e-mail: [EMAIL PROTECTED] > > -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
