MEMBERSHIP(membership_number (pk), cover_type(pk),
start_date(pk), end_date)
PERSON(person_number(pk), surname, initials, street_address, suburb, ,
membership_number)
DOCTOR(doctor_number(pk), surname, initials, street_address, suburb)
PROVIDED-SERVICE(person_number(pk), doctor_number(pk), service_date(pk),
service_number)
REBATE(service_number(pk), cover_type(pk), refund)
SERVICE(service_numbe(pk)r, service_name, service_description)
the cover_types are single, couple, family
the question is
List the service-names and maximum refund payable for those services
which have been provided to every person who has visited a doctor.
this query asks for only those services provided to every person
For each such services, list the name of the service and the maximum
refund for that service (i.e. the same service can have many different
refunds, only list the highest refund). Maximum refund therefore refers
to the maximum for each such service, not a maximum across all services.
i came with the following query
SELECT s.service_name, MAX(r.refund) as maximum_refund
FROM rebate r,service s,provided_Service ps,doctor d
WHERE r.service_number = s.service_number AND
s.service_number = ps.service_number AND
ps.doctor_number = d.doctor_number
GROUP BY s.service_name;
any help in this regard is very much appreciated .
thank you.
--
Posted via http://dbforums.comIf I've understood you correctly the only modification required to your
query is the additional criteria to include only services that have been
provided to everyone, correct?
If so, try this:
SELECT S.service_name, SUM(R.refund) AS maximum_refund
FROM Rebate AS R
JOIN Service AS S
ON R.service_number = S.service_number
JOIN Provided_Service AS P
ON S.service_number = P.service_number
JOIN Doctor AS D
ON P.doctor_number = D.doctor_number
WHERE S.service_number IN
(SELECT service_number
FROM Provided_Service
GROUP BY service_number
HAVING COUNT(DISTINCT person_number) =
(SELECT COUNT(*)
FROM Person))
GROUP BY S.service_name
If this doesn't answer your question then please post DDL (CREATE TABLE
statements) for your tables, include some sample data as INSERT statements
and show an example of your required result.
--
David Portas
----
Please reply only to the newsgroup
--|||CORRECTION:
SELECT S.service_name, MAX(R.refund) AS maximum_refund
...
--
David Portas
----
Please reply only to the newsgroup
--|||Here's a better alternative to my first effort:
SELECT S.service_name, MAX(R.refund) AS maximum_refund
FROM Rebate AS R
JOIN Service AS S
ON R.service_number = S.service_number
JOIN Provided_Service AS P
ON S.service_number = P.service_number
JOIN Doctor AS D
ON P.doctor_number = D.doctor_number
GROUP BY S.service_name, S.service_number
HAVING COUNT(DISTINCT P.person_number) =
(SELECT COUNT(*)
FROM Person)
--
David Portas
----
Please reply only to the newsgroup
--
