title: SQL inner join with max function
body: <p>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. </p>
  
  <pre><code>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
  </code></pre>