T-SQL: Conditional join or convoluted WHERE clause?

Question

I have a table called MapObjects which is used to store information about objects placed on a map. I have another table called OrgLocations which is used to store all the locations where an organisation is located. Locations are defined with a latitude and longitude. Finally, I have another table called ObjectLocations which maps a map object to an organistion in the OrgLocations table. It is used to indicate a subset of the locations for an object that is shown on a map.

As an example, suppose an organisation (OrgID = 10) has 4 locations (stored in the OrgLocations table): Dallas, Atlanta, Miami, New York. The organisation has 1 map object associated with Atlanta and Miami (MapObjects.ID = 5).

My dataset must return the records from OrgLocations that correspond with Atlanta and Miami (but not include Dallas or New York) . However, I can also have a map object that is not assigned to any location (no record in ObjectLocations). These map objects still belong to an organisation but are not associated with any specific location. In this case I want to return all the locations assigned to the organisation.

I am not sure if this is done through a conditional join or something in the WHERE clause. Here is what the tables would look like with some data:

OrgLocations

ID   OrgID     Latitude      Longitude     Name
0     10        32.780        -96.798      Dallas
1     10        33.7497       -84.394      Atlanta
2     10        25.7863       -80.2270     Miami
3     10        40.712        -74.005      New York
4     11        42.348        -83.071      Detroit

ObjectLocations

OrgLocationID      MapObjectID
1                      5
2                      5

MapObjects

ID      OrgID
5        10
6        11

In this example, when MapObjects.ID is 5, 2 locations for this object exist in ObjectLocations: Atlanta and Miami. When MapObjects.ID is 6, there is no record in ObjectLocations so all the locations in OrgLocatons that belong to the organisation (OrgID = 11) are returned.

Thanks for any help!

Mikael Eriksson · Accepted Answer · 2012-04-30 09:02:39Z

2

I guess you will have the cleanest queries if you check for the existence of MapObjectID in ObjectLocations to decide what query to use.

Something like this:

declare @MapObjectID int
set @MapObjectID = 5

if exists(select * 
          from ObjectLocations 
          where MapObjectID = @MapObjectID)
begin
  select *
  from OrgLocations
  where ID in (select OrgLocationID 
               from ObjectLocations
               where MapObjectID = @MapObjectID)
end
else
begin
  select *
  from OrgLocations
  where OrgID in (select OrgID
                  from MapObjects
                  where ID = @MapObjectID)
end

As a single query.

select OL.*
from OrgLocations as OL
  inner join ObjectLocations as OLoc
    on OL.ID = OLoc.OrgLocationID
where OLoc.MapObjectID = @MapObjectID
union all
select OL.*
from OrgLocations as OL
  inner join MapObjects as MO
    on OL.OrgID = MO.OrgID
where MO.ID = @MapObjectID and
      not exists (select *
                  from ObjectLocations
                  where MapObjectID = @MapObjectID)

edited Apr 30, 2012 at 9:02

answered Apr 30, 2012 at 8:13

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mikael Eriksson Over a year ago

@aF I guess you are referring the the where ... in join. I felt a bit unsafe using joins without knowing the primary keys / unique constraints on the tables involved. This way there will be no duplicate locations in the output.

Johann Over a year ago

I actually need to include the resultset as a subquery, so your If...else won't work.

Mikael Eriksson Over a year ago

@AndroidDev Store the result in a table variable or a temp table and use that in your subquery.

Johann Over a year ago

I'm sure the solution can be done strictly with joins, Exists and a WHERE clause but I'm not sure how.

Johann Over a year ago

Thanks a million! Your solution with joins is the best. What I was missing in my thinking was the use of the UNION statement.

Collectives™ on Stack Overflow

T-SQL: Conditional join or convoluted WHERE clause?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related