Reporting Three Very Badly Joined Tables

[bombplayer]

I am writing a report on a proprietary software platform in SQL that has a very weird table structure. Basically, I have these fields to work with:

Table 1: School Name, School ID
Table 2: Student,Student ID, Advert ID, School ID, Lead Date
Table 3: Advertising Cost, Advert ID, School ID, Ad Date

Table 2 and Table 3 could have entries that are not included in the other table.

I need to compile a report that shows:

School Name
Advert ID
Number of Students Sum of Advertising Cost

Both of these sums need to be within a date range submitted by the user where lead date in date range and ad date in date range and totalled at the bottom of the report. I have tried a SQL subquery for both to no avail. I can get the report to report all students but will not report all costs (if there are no students associated with it) or vice versa. Any help would be appreciated.

 

[rogar]

SELECT Table1.School Name, Table2.AdvertID AS "AdvertID", COUNT(Table2.Studentid) AS "NumberOfStudents", Sum(Table3.Advertising Cost) FROM (Table2 INNER JOIN Table1 ON Table1.SchoolID = Table2.SchoolID) LEFT JOIN Table3 on Table2.AdvertID = Table3.AdvertID
GROUP BY Table1.School Name, Table2.AdvertID
UNION
SELECT Table1.School Name, Table3.AdvertID AS "AdvertID", 0 AS "NumberOfStudents", Sum(Table3.Advertising Cost) FROM
Table3 INNER JOIN Table1 on Table3.SchoolID = Table1.SchoolID WHERE Table3.AdvertID NOT IN
(SELECT DISTINCT AdvertID FROM Table2)
GROUP BY Table1.School Name, Table3.AdvertID

 

[bombplayer]

Didn't get my there but got me going in the right direction. Thanks a mill!