SQL Query to find Records in Table1 that has one relation to Table2 but should have other relation to the same Table2

Question

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

score 2 · Answer 1 · answered Oct 27 '21 at 13:36

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

Nikhil Agrawal · Answer 2 · 2021-10-27T13:37:43.260

0

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

edited Oct 27 '21 at 13:37

answered Oct 27 '21 at 13:32

Nikhil Agrawal

47,018
22
121
208

Thank you for your response. I have considered this. But this will give Physical locations, one of whose stores might be active also. Might not be in this result set. – sqloverflow Oct 27 '21 at 13:38
@sqloverflow - so you need a query that should give a location only when all the stores in it are inactive? – Nikhil Agrawal Oct 27 '21 at 13:40
Yes, that is correct. – sqloverflow Oct 27 '21 at 13:46
Check @ggordon's answer. Its closer to what you want. – Nikhil Agrawal Oct 27 '21 at 14:03

ggordon · Answer 3 · 2021-10-27T14:00:46.157

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

View working demo db fiddle

Thank you @ggordon , this is close to what I was looking for. I learnt few things today ! — sqloverflow, Oct 27 '21 at 14:30

score 0 · Answer 4 · answered Oct 27 '21 at 13:52

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

score 0 · Answer 5 · answered Oct 27 '21 at 14:07

You need to select what locations have active stores and filter out those locations in the WHERE clause.

SELECT S.LocationId
    ,LocationAddress
FROM #Store S
    INNER JOIN #PhysicalLocation PL
        ON S.StoreID = PL.LocationId
WHERE S.LocationId NOT IN (SELECT LocationId FROM #Store WHERE [status] = 'active')

OUTPUT:

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.

Shreekesh Murkar · Answer 6 · 2021-10-27T14:30:44.203

0

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')
)

edited Oct 27 '21 at 14:30

answered Oct 27 '21 at 14:11

Shreekesh Murkar

565
4
9

Why `DISTINCT`, what purpose does it serve in a semi-join? And it's not a function, so why `(LocationId)` – Charlieface Oct 27 '21 at 15:48
Distinct will reduce list that we want to compare with. Without Distinct we will get same LocationId twice – Shreekesh Murkar Oct 28 '21 at 04:26
You won't, because you're doing a semi-join with that `IN` clause, all it says is: are there any matching `LocationId` in the subquery, you don't need to `DISTINCT` the subquery – Charlieface Oct 28 '21 at 09:11
This is needed not because of the data query that we have in 'IN' clause but because the data that we have in Store table – Shreekesh Murkar Oct 28 '21 at 09:45
Again `where LocationId in (select Distinct LocationId from Store` and `where LocationId in (select LocationId from Store` is *the same thing* because all it says is: are there *any* matches in the subquery, it makes no difference how many matches so you don't need to "reduce it down", it's not a join, it's a semi-join, see also https://stackoverflow.com/questions/42249690/what-is-semi-join-in-database – Charlieface Oct 28 '21 at 10:00

SQL Query to find Records in Table1 that has one relation to Table2 but should have other relation to the same Table2

6 Answers6