Mulitple left outer jons

[catbert]

Hi, I am hoping someone can help point me in the right direction.

I have a table of streets (central_site)
Against this I need to run a report to show ( for a single street ) whether that street has had any jobs, enquiries or defects in a set time period.

the associated tables for this information are

job
defect
central_enquiry

For the chosen street (it is only ever one street) there may or may not be records in each of these tables. So I have left joined each of these tables on a unique street ID which exists in all tables (site code). This is just a sample of the required fields - the full report will need details of the jobs, enquiries and defects etc if they exist:

SELECT
central_site.site_name,
central_site.site_code,
job.job_number,
defect.defect_number,
central_enquiry.Enquiry_number
FROM
(((central_site
LEFT OUTER JOIN job ON job.site_code = central_site.site_code)
LEFT OUTER JOIN defect ON defect.site_code = central_site.site_code)
LEFT OUTER JOIN central_enquiry ON central_enquiry.site_code = central_site.site_code)

This works but not very well. As you would expect I get multiple rows of each combination which makes the report slow to run, and to look at a date range - say in the last year - the user has to select both the street name - and then the date range 3 times - one for each linkied table.

So is there a better way to retrieve this data - especially if it would allow only one date range input - as my solution seems very unwieldy.

thanks
CB
Sample data output currently:
Site Job defect enquiry
38104951 1172 27 963
38104951 1172 27 1871
38104951 1172 27 6956
38104951 1172 27 9370
38104951 1172 27 11524
38104951 1172 27 11806

 

[SkipVought]

Where are the dates???

 

[Olaf Doschke]

This should be done with unions, not joins. Classical case. Or even simpler, instead of one result you need 3, you have three lists for each street, so query and report it that way.

Bye, Olaf.

 

[djj55]

Are you looking for something like this:

SELECT
    central_site.site_name,
    central_site.site_code,
    job.job_number,
    defect.defect_number,
    COUNT(central_enquiry.Enquiry_number) AS EnqCount
FROM central_site
LEFT OUTER JOIN job ON job.site_code = central_site.site_code
LEFT OUTER JOIN defect ON defect.site_code = central_site.site_code
LEFT OUTER JOIN central_enquiry ON central_enquiry.site_code = central_site.site_code
GROUP BY central_site.site_name,
    central_site.site_code,
    job.job_number,
    defect.defect_number;

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!