Connecting and Getting data from 8 tables in one sql statement

Hi everybody,

I like to get data from 8 tables to be loaded in a dropdownlist or combobox control. Desired Result is need to be filtered by Region, Company, ProjectOffice, Country, and Location and still get all rows from 3 tables after applying filters. How can this be done in one single sql statement?

I have 3 separate sql statements which needed to be only one.

Select C.CompanyID, C.[Name] As 'Company', P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID,
R.[Name] As 'Region'
From ProjectCompany E Left Join ProjectOffice P On E.ProjectOfficeID = P.ProjectOfficeID
Left Join Company C On E.CompanyID = C.CompanyID
Left Join Region R On P.RegionID = R.RegionID Order By E.ProjectOfficeID

Select P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID, R.[Name] As 'Region',
C.CountryID, C.[Name] As 'Country'
From ProjectOfficeCountry O Left Join ProjectOffice P On O.ProjectOfficeID = P.ProjectOfficeID
Left Join Country C On O.CountryID = C.CountryID
Left Join Region R On P.RegionID = R.RegionID

Select P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID, R.[Name] As 'Region',
W.LocationID, W.[Name] As 'Location'
From ProjectOfficeLocation L Left Join ProjectOffice P On L.ProjectOfficeID = P.ProjectOfficeID
Left Join Location W On L.LocationID = W.LocationID
Left Join Region R On P.RegionID = R.RegionID

Table structures (some columns)

Region table
RegionID Name

ProjectOffice table

ProjectOfficeID Name RegionID

Location Table

LocationID Name

Country table

CountryID Name

Company table

CompanyID Name

ProjectCompany table (All rows)

ID ProjectOfficeID CompanyID

ProjectOfficeCountry table (All rows)

ID ProjectOfficeID CountryID

ProjectOfficeLocation table (All rows)

ID ProjectOfficeID LocationID

maybe something like this ?

Select 'A' as [type], C.CompanyID, C.[Name] As 'Company', P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID,
R.[Name] As 'Region'
From ProjectCompany E Left Join ProjectOffice P On E.ProjectOfficeID = P.ProjectOfficeID
Left Join Company C On E.CompanyID = C.CompanyID
Left Join Region R On P.RegionID = R.RegionID Order By E.ProjectOfficeID

UNION ALL

Select 'B' as [type], P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID, R.[Name] As 'Region',
C.CountryID, C.[Name] As 'Country'
From ProjectOfficeCountry O Left Join ProjectOffice P On O.ProjectOfficeID = P.ProjectOfficeID
Left Join Country C On O.CountryID = C.CountryID
Left Join Region R On P.RegionID = R.RegionID

UNION ALL

Select 'C' as [type], P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID, R.[Name] As 'Region',
W.LocationID, W.[Name] As 'Location'
From ProjectOfficeLocation L Left Join ProjectOffice P On L.ProjectOfficeID = P.ProjectOfficeID
Left Join Location W On L.LocationID = W.LocationID
Left Join Region R On P.RegionID = R.RegionID

|||

Thanks carlop for replying,

If I did that then I could not differentiate between CompanyID and ProjectID or Company From Project Office or Location From Region, there should be a better way to do this...

Thanks.

|||

I really don’t understand why you need in single SQL Statement. Are you trying to fetch those result in single batch (single hit to server).

If you don’t use the union all then you have to use the JOIN. It may lead your (desired) result set to duplicates (for region & country) and it is not helpful on Combobox binding.

|||

ManiD,

Trying to get say data for Company, filtered by Region,Country, ProjectOffice, Company and Location, then data will be used to fill the combobox or dropdownlist control.

|||

are you like to hit the db server once to get those data.

or

you need to have all the data in columns..?

|||

ManiD,

Well, that is ideal to get all columns I needed and without duplicates. I have used several sql statements to achive same result, I wonder if it can be done using one single sql statement.

|||

While I agree with the other posters that this is not a very good idea, you could probably use union to put all of the data in a single result:

Select C.CompanyID, C.[Name] As 'Company', P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID,
R.[Name] As 'Region', 'PURPOSE1' as purpose
From ProjectCompany E Left Join ProjectOffice P On E.ProjectOfficeID = P.ProjectOfficeID
Left Join Company C On E.CompanyID = C.CompanyID
Left Join Region R On P.RegionID = R.RegionID

UNION ALL

Select P.ProjectOfficeID, P.[Name] As 'Project Office', P.RegionID, R.[Name] As 'Region',
C.CountryID, C.[Name] As 'Country', 'PURPOSE2' as purpose
From ProjectOfficeCountry O Left Join ProjectOffice P On O.ProjectOfficeID = P.ProjectOfficeID
Left Join Country C On O.CountryID = C.CountryID
Left Join Region R On P.RegionID = R.RegionID

Note that you would also need to genericize the other names, like the id, the name, etc. I almost feel wrong in suggesting this, because it seems so much cleaner to make N result sets and get the data one set at a time (much cleaner and certainly eaiser to implement.)

|||

Thanks for the reply Louis.

It seems there is no exact way getting all data needed in just one sql statement. I would stick to using multiple sql statements in getting the results.

Thanks.

I think the issue in this thread is closed.