SQL inner join with max function

Question

i have some problem in this query i want to see who paid Max fees to which doctor? i have created 3 tables name are tblPatient,tblClinic and tblDoctor now i have set the foreign keys on tblClinic table in PID and DID column now what can i do in this query so i will get result which i want.

create database Hospital
use Hospital

create table tblPatient
(
PID int identity(100,1) primary key not null,
PName varchar(20),
PPhone varchar(20)
)

create table tblClinic
(
CID int identity(300,1) primary key not null,
PID int,
DID int,
Fees int,
foreign key(PID) references tblPatient(PID),
foreign key(DID) references tblDoctor(DID)
)

create table tblDoctor
(
DID int identity(200,1) primary key not null,
DName varchar(20),
DPhone varchar(20),
)

insert into tblPatient values('Zeeshan',033314785)
insert into tblPatient values('Mufaddil',034521548)
insert into tblPatient values('Shayan',033236981)
insert into tblPatient values('Zahid',0334425632)
insert into tblPatient values('Afzaal',030178945)
insert into tblClinic values (100,201,1500)
insert into tblClinic values (102,202,1600)
insert into tblClinic values (103,204,700)
insert into tblClinic values (101,201,800)
insert into tblClinic values (101,200,600)
insert into tblClinic values (103,202,650)
insert into tblClinic values (104,202,550)
insert into tblClinic values (102,203,840)
insert into tblClinic values (101,203,2000)
insert into tblClinic values (100,204,250)
insert into tblClinic values (100,201,1700)
insert into tblClinic values (101,202,1650)
insert into tblClinic values (104,204,300)
insert into tblDoctor values ('Dr.Amir',033412345)
insert into tblDoctor values ('Dr.Bilal',034554125)
insert into tblDoctor values ('Dr.Saim',033358741)
insert into tblDoctor values ('Dr.Rizwan',033325871)
insert into tblDoctor values ('Dr.Kamran',030025874)

select * from tblPatient
select * from tblClinic
select * from tblDoctor

select p.PName,d.DName
from tblPatient p
inner join
tblClinic c
ON p.PID=c.PID
inner join
tblDoctor d
ON d.DID=c.DID
where PName in ('Zeeshan','Shayan','Afzaal')

select d.DName,p.PName
from tblDoctor d
inner join
tblClinic c
ON d.DID=c.DID
inner join
tblPatient p
ON p.PID=c.PID
where d.DName in ('Dr.Bilal','Dr.Kamran','Dr.Amir')
order by d.DName,p.PName asc

select d.DName,p.PName,c.Fees
from tblDoctor d
inner join
tblClinic c
ON d.DID=c.DID
inner join
tblPatient p
ON p.PID=c.PID
where d.DName in ('Dr.Bilal','Dr.Kamran','Dr.Amir')
order by d.DName,p.PName asc

Answer 1

If you want to find which patient has paid the maximum fee for each of the doctors, this query should do:

SELECT P.PName, D.DName, C.Fees
FROM tblClinic C
INNER JOIN tblPatient P ON P.PID = C.PID
INNER JOIN tblDoctor D ON D.DID = C.DID
WHERE C.Fees =
(SELECT max(Fees) FROM tblClinic C2
WHERE C2.DID = C.DID)