Query that Selects data from 3 tables in best way possible

[Link82]

Hey Everyone,

So after weeks of torture, this things is near completion. We're using SSRS to display the report on the .NET side. However, I'm piecing together the last bits of the SQL query. So, I have three tables. One has names of all these buildings, one has contains reason_codes, and the last one has three fields: building, reason code, extra_pay.

The users need the report in a certain format. For instance, on the left side, top to bottom, they want ALL the reason codes, on top from left to right, they want all the buildings (think of an excel type report). Then, the extra_pay is shown where a reason code and building meet.

Let's call the tables tblBuildings, tblReasonCodes, and tblMain.

What would be the best way to return all this data? Keep in mind that reason codes don't relate to buildings. For instance, there is no mapping of building a to certain reason codes. Any reason code can be applied to any building, and vice versa.

If I do a simple query like A (not complete), then for each building, I get every work reason code. So if I have 10 buildings and 15 reason codes, then 10 * 15 = 150 rows. I just need the ten buildings, the 15 codes, and my final data from tblMain mapped to it.

Make sense? Best way to do this, thanks :)

[Link82]

By A I meant:

--A
SELECT	DISTINCT	b.Buildings, c.reason_code
FROM	common.dbo.tblBuildings b, c.reason_code

[Link82]

Err, I can't be asking anything extra-ordinary. I'm not well-versed with SQL, so if anyone has questions about what I'm asking, please le tme know. Still need help... thanks.

[Link82]

Err, I can't be asking anything extra-ordinary. I'm not well-versed with SQL, so if anyone has questions about what I'm asking, please le tme know. Still need help... thanks.

Here's an image. See how the numbers are repeated on left? Don't want that. No repetition :(

Here's my current query:

SELECT		s.School_Name, c.reason_code, SUM(m.extra_pay) as overtime_pay
FROM		common.dbo.tblSchool s --gives me listing of all buildings
 
LEFT OUTER JOIN	tblOvertimeReasonCodes c --gives listing of all reason codes
on		c.district like '16'
 
LEFT OUTER JOIN	#main M --my table that holds overtime_pay by building and reason_code
on	                    m.work_loc = s.school
and		m.work_reason_code = c.reason_code	
							
GROUP BY		s.School_Name, c.reason_code
ORDER BY		c.reason_code, s.School_Name  asc

[sknake]

Can you upload an excel spreadsheet with enough test data to reproduce the issue? I'm afraid i'm not following your description. It gets a little confusing with three tables and not seeing the structure or data.