David Burrows (3/8/2013)
I was thinking more on line of
SELECT XX.XY, XX.YY, XX.YX
FROM Properties
JOIN XX ON XX.RMPROPID = Properties.RMPROPID
WHERE Properties.RMPROPID IN (@PROPERTIES)
Which will give you one row per property and each column required in the report (i.e. XY = Units)
This is only guesswork since you have not supplied any DDL or sample data as requested by others.
+1 -- I agree -- this way the data are related by RMPROPID. Also, David Burrows's example query as an idea may not necessarily return one row per property, however, but at the very least, all data related to a specific RMPROPID will be linked together correctly and reliably, which means that aggregating values together by RMPROPID would be correct.
Lastly, if multiple tables need to be referenced, but not all tables have data for a given RMPROPID, you'll want to use outer joins (.e.g, Left Join) from a core table (e.g., Properties) to your related tables (e.g. XX).