so u have two sets of prices. one in the service table and one in the employee table, is that correct. And if a service is listed under both the employee and the customer, then you want to us ethe price listed in the employee table and not in the service table.
-----Original Message----- From: Tim Blair [mailto:[EMAIL PROTECTED]] Sent: 12 September 2002 14:57 To: [EMAIL PROTECTED] 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]
