SQL Query to find Records in Table1 that has one relation to Table2 but should have other relation t-CodePudding

We have two tables for "Physical Location" and "Stores". One Physical Location can have multiple stores. Each Store can be either Active or Inactive.

How would we write a query to find those 'Physical Locations' that has no active Stores associated to it?

PhysicalLocation

LocationId    LocationAddress
100           Address1
101           Address2
102           Address3

Store

StoreID    LocationId        Status
100A           100           Active
100B           100           Inactive
101C           101           Inactive
102D           101           Inactive

I have tried something like the below.

Select * from PhysicalLocation where LocationId IN 
 (Select LocationId from Store where Status <> 'Active')

My expected result is

LocationId    LocationAddress
    101           Address2

Since this location has only inactive stores

CodePudding user response：

select l.LocationId,l.LocationAddress
from locations as l
where not exists
(
  select 1 from stores as s
    where l.LocationId=s.LocationId
     and s.Status='Active'
)

Could you please try this one if it is suitable for you. By the way, location 102 also does not contain active stores

CodePudding user response：

Use Joins

SELECT * FROM PhysicalLocation 
INNER JOIN Store ON Store.LocationId = PhysicalLocation.LocationId
WHERE Store.Status = 'Inactive'

I have used INNER JOIN with the assumption that no store locations e.g 102 Address3, is as good as the location with inactive store(s).

If the assumption is incorrect and you want all inactive along with locations where there are no stores, then go for LEFT OUTER JOIN

CodePudding user response：

The query will be like this.

SELECT * FROM PhysicalLocation AS Ph INNER JOIN Store AS St ON Ph.LocationId = St.LocationId WHERE St.Status = 'Inactive';

Use Join to make relation between two tables. This is will give you resultant values you are looking for.

Output:

LocationId    LocationAddress    StoreID        LocationId    Status
100           Address1           100B           100           Inactive
101           Address2           101C           101           Inactive
101           Address2           102D           101           Inactive

CodePudding user response：

You may use the following which uses a having clause with a case expression in a count to filter out locations that have active stores. The join is used to retrieve additional location details.

SELECT
    P.*
FROM
   PhysicalLocation P
INNER JOIN (
   SELECT LocationId
   FROM Store
   GROUP BY LocationId
   HAVING COUNT(CASE WHEN Status='Active' THEN 1 END) =0
) SA ON p.LocationId=SA.LocationId

LocationId	LocationAddress
101	Address2

If you are interested in all locations with no active stores regardless of whether a store entry has ever been created then you may try the following which uses a left join and where clause to apply the filter instead of an inner join as above.

SELECT
    P.*
FROM
   PhysicalLocation P
LEFT JOIN (
   SELECT 
       LocationId,
       COUNT(CASE WHEN Status='Active' THEN 1 END) as no_active_locations
   FROM Store
   GROUP BY LocationId
) SA ON p.LocationId=SA.LocationId
WHERE SA.no_active_locations=0 OR SA.no_active_locations IS NULL

LocationId	LocationAddress
101	Address2
102	Address3

This is slightly different than the desired output you specified, but I suspect you did not account for the additional address. There is still only 1 location with no active stores.

CodePudding user response：

Here is the simplest solution :

select LocationId, LocationAddress from PhysicalLocation
where LocationId in 
(
    select Distinct (LocationId) from Store 
    where LocationId not in (select LocationId from Store where Status = 'Active')
)