Hi,

I'm have a query that will pull a list of consultants and match their skills against job postings and display ones that meet a certain percentage of criteria. For example, let's say a job requires typing, Office, SQL, and Photoshop - but I only have typing, Office, and SQL - my percentage match would be 75%.

Consultants have the ability to request email notifications about new positions, so long as they meet a certain percentage of criteria. The minimum level is something they can choose and would vary from person to person. In order to populate this list to send out the notifications, I filter my results where minimum skill level >= their compatibility.

Unfortunately the SQL I've written is functional, but probably very poorly constructed. Could anyone give me advice on how to clean it up and optimize it?

Thanks!

Code:
SELECT tblConsultants.ConsultantID, CompatNotify, 
(CAST(((SELECT Count(JoinExpertiseID) FROM JoinConsultants2Expertise 
	INNER JOIN JoinPost2Expertise ON JoinPost2Expertise.ExpertiseID = JoinConsultants2Expertise.ExpertiseID
	WHERE ConsultantID=tblConsultants.ConsultantID) +

(SELECT Count(JoinLanguageID) FROM JoinConsultants2Language
	INNER JOIN JoinPost2Language ON JoinPost2Language.LanguageID = JoinConsultants2Language.LanguageID
	WHERE ConsultantID=tblConsultants.ConsultantID) +

(SELECT Count(JoinRegionID) FROM JoinConsultants2Region 
	INNER JOIN JoinPost2Region ON JoinPost2Region.RegionID = JoinConsultants2Region.RegionID
	WHERE ConsultantID=tblConsultants.ConsultantID)) AS Decimal) /

CAST(((SELECT Count(JoinExpertisePostID) FROM JoinPost2Expertise WHERE ConsultantPostID=1) + 
(SELECT Count(JoinLanguagePostID) FROM JoinPost2Language WHERE ConsultantPostID=1) +
(SELECT Count(JoinRegionPostID) FROM JoinPost2Region WHERE ConsultantPostID=1)) AS Decimal) * 100) AS ConsultantCompat

FROM tblConsultants 
WHERE EmailNotify=1 AND Active=1 AND Deleted=0
AND CompatNotify <= (CAST(((SELECT Count(JoinExpertiseID) FROM JoinConsultants2Expertise 
	INNER JOIN JoinPost2Expertise ON JoinPost2Expertise.ExpertiseID = JoinConsultants2Expertise.ExpertiseID
	WHERE ConsultantID=tblConsultants.ConsultantID) +

(SELECT Count(JoinLanguageID) FROM JoinConsultants2Language
	INNER JOIN JoinPost2Language ON JoinPost2Language.LanguageID = JoinConsultants2Language.LanguageID
	WHERE ConsultantID=tblConsultants.ConsultantID) +

(SELECT Count(JoinRegionID) FROM JoinConsultants2Region 
	INNER JOIN JoinPost2Region ON JoinPost2Region.RegionID = JoinConsultants2Region.RegionID
	WHERE ConsultantID=tblConsultants.ConsultantID)) AS Decimal) /

CAST(((SELECT Count(JoinExpertisePostID) FROM JoinPost2Expertise WHERE ConsultantPostID=1) + 
(SELECT Count(JoinLanguagePostID) FROM JoinPost2Language WHERE ConsultantPostID=1) +
(SELECT Count(JoinRegionPostID) FROM JoinPost2Region WHERE ConsultantPostID=1)) AS Decimal) * 100)

ORDER BY ConsultantID
Any help or suggestions would be appreciated - I'm not very familiar with these types of queries and I'd like to learn how to write them better.

Thanks!
-T