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.
