>In VFP with SetEngineBehavior 70 you can get away with something like this:
>
>

>SELECT partmast.partnum,
>	partmast.title,
>	partmast.description,
>	NVL(partstats.emplviews+partstats.intviews,0000000) as Total_Views,
>	COUNT(invitems.invnum) as sales_count 
>FROM partmast 
>	LEFT OUTER JOIN partstats 
>		ON partmast.partnum=partstats.partnum 
>	LEFT OUTER JOIN invitems 
>		ON partmast.partnum=invitems.partnum
>WHERE partmast.partnum='12345' 
>GROUP BY pk
>ORDER BY partnum
>
>

>
>How can I do the equivalent in T-SQL? So far I have:
>
>

>SELECT partmast.partnum,
>	partmast.title,
>	ISNULL(partstats.emplviews+partstats.intviews,0000000) as Total_Views,
>	COUNT(invitems.invnum) as sales_count 
>FROM partmast 
>	LEFT OUTER JOIN partstats 
>		ON partmast.partnum=partstats.partnum 
>	LEFT OUTER JOIN invitems 
>		ON partmast.partnum=invitems.partnum
>WHERE partmast.partnum='12345'
>GROUP BY partmast.partnum,partmast.title
>ORDER BY partnum
>
>

>
>But it complains and wants me to include partstats.emplviews which may or may not exists... thus the ISNULL
>
>partmast is the master part table
>
>partstats is a stats table that may not have a record for each partmast record, but it will never have more than one record per partmast.partnum
>
>invitems is a many table that can have zero or hundreds of records with the partmast.pk
>
>Suggestions?

What is your SQL Server version? Use derived table approach (same in VFP 9 as well):

SELECT partmast.partnum,
	partmast.title,
	ISNULL(partstats.emplviews+partstats.intviews,0000000) as Total_Views,
	X.Sales_count 
FROM partmast 
	LEFT OUTER JOIN partstats 
		ON partmast.partnum=partstats.partnum 
	LEFT OUTER JOIN (select PartNum, count(InvNum) as Sales_Count from invitems group by PartNum) X
		ON partmast.partnum=X.partnum
WHERE partmast.partnum='12345'
ORDER BY partnum