How do I return distinct fields from one tbl that are left joined to fields from another table and a query?
Posted on 2011-03-16
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;