How do I return distinct fields from one tbl that are left joined to fields from another table and a query?

I have a table of Consultants each with a ConsultantID and a table of Contracts each with a ContractID and a ConsultantID. Each ContractID has a MaxDailyRate

I want to see every a MaxDailyRate for each Consultant, but when I query using the SQL below to get distinct ConsultantIDs to join to the MaxDailyRate, I all the ContractIDs and duplicate ConsultantIDs

SELECT DISTINCT tblConsultants.ConsultantID, tblContracts.ConsultantID,
FROM tbl Consultants LEFT JOIN tbl Contracts On tblConsultants.ConsultantID = tblContracts.ConsultantID;

Any ideas?
melodymurrayAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this

select C.ConsultantID, Con.MaxRate
from tblConsultants as C Inner join
(select max(MaxDailyRate) as MaxRate,ConsultantID from tblContracts as TC
 group by ConsultantID)
as Con
On C.ConsultantID=Con.ConsultantID
0
 
peter57rCommented:
I think you may have top re-state what you really want, because what you say you want doesn't require two tables.

"I want to see every a MaxDailyRate for each Consultant"

Select distinct Consultantid, maxdailyrate from contracts order by consultantid
0
All Courses

From novice to tech pro — start learning today.