The following query returns 11,869 records, but the table "tprod" has 14,752. Shouldn't the Right Join force the query to return 14,752 rows, even if 2,883 of them have no matching data in tURMSReport?
Code:
SELECT T1.Product, T1.Business_Unit, T1.ProductDesc, T1.NDC11, T1.State, T1.State_Category, SUM(T1.iMANY_FFS_Units) AS iMANYFFSUnits, SUM(T1.iMANY_MCO_Units) AS iMANYMCOUnits, SUM(T1.ffs_Units) AS FFSUnits, SUM(T1.mco_Units) AS MCOUnits
FROM (tURMSReport T1
RIGHT OUTER JOIN
tProd T2
ON T1.NDC11=T2.NDC11 AND T1.State=T2.State)
WHERE T1.Quarter='Q1_2008'
GROUP BY T1.Product, T1.Business_Unit, T1.ProductDesc, T1.NDC11, T1.State, T1.State_Category
ORDER BY T1.Product, T1.State, T1.NDC11;
