help very urgent

[hallosenthil]

the relations are

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.com

[David Portas]

If 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
--

[David Portas]

CORRECTION:

SELECT S.service_name, MAX(R.refund) AS maximum_refund
...

--
David Portas
------------
Please reply only to the newsgroup
--

[David Portas]

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
--