First off, Hi guys! I've missed being active but super crazy busy. Anyway, I'm having a problem figuring out how to construct a query to get the desired result set. Right now, I'm basically executing 3 queries and combining and filtering the result sets.
Here is the simplified data structure:
Create table Organization ( OrganizationId int not null auto_increment primary key, name varchar(50));
create table Location ( LocationId int not null auto_increment primary key, address varchar(100));
create table Service ( ServiceId int not null auto_increment primary key, name varchar(50));
create table OrganizationLocation ( OrgLocationId int not null auto_increment primary key, OrganizationId int not null, LocationId int not null);
create table OrganizationService ( OrgServiceId int not null auto_increment primary key, OrganizationId int not null, ServiceId int not null);
create table OrgServLocation ( OrgServLocationId int not null auto_increment primary key, OrgServiceId int not null, OrgLocationId int not null);
create table LocationService ( LocationServiceId int not null auto_increment primary key, LocationId int not null, ServiceId int not null);
Now there are 3 dropdowns on the screen: Organization, Location, Service. What I need to do is select all services offered at a certain location, by a certain organization or both. There are rules though:
1) LocationService holds services provided by all organization at that location.
2) OrganizationLocation holds locations where an organization provides services
3) OrganizationService holds all services that an organization provides
4) OrgServLocation holds services provided at a specific organization, if an OrganizationService is not referenced in this table, then that service is provided at all locations.
I'm at an absolute complete loss at how to filter on Location, and LocationOrganization in a single query. Is this even possible?
Currently, I am getting all service in LocationService for a certain location id. Adding those to the results of getting all organizationService at OrgServLocation for a specific location. Finally adding any service that are in OrganizationService with no entries in OrgServLocation. Then putting the 3 arrays together and removing dups. It seems there must be a better way. I'm doing very similar when provided an organizationId as well, except the second 2 queries both have AND Organizationid = selected value when that's needed. If you want to see the code for this just ask and I will mock something up 🙂
