Use inner join if record exists otherwise use left join

Question

I have the following table structure:

dbo.Owner

OwnerID   OwnerName  
  1        John   
  2        Marie
  3        Alex

and dbo.Pet

PetID PetTag Status OwnerID
  1    A341  Active    1  
  2    A342  Inactive  1  
  3    A343  Active    2
  4    A345  Active    2

I need to return all owners who have only Active pets or no pets.

So in this example above I need to Return Owner 2 (All pets are active) and Owner 3 (No pets)

I will be pulling data in C# using Entity Framework but plain SQL will be sufficient.

Here's what I came up with so far:

select mi.* from Owner o
join Pet p
on o.OwnerID= p.OwnerID
where o.Status='Active'
union select * from Owner
where OwnerID not in (select OwnerID from Pet)

Now, this query above works but it includes OwnerID = 1. and Also I was wondering if there's a way to do this in 1 query without union.

Answer 1

If your only values for Status are "Active" and "Inactive", you can actually simplify your query. When you say:

I need to return all owners who have only Active pets or no pets.

This would then actually translate to:

I need to return all owners who have no Inactive pets.

Then your query becomes much easier.

In an Entity Framework query:

owners = context.Owners
    .Where(o => !o.Pets.Any(p => p.Status == "Inactive"))
    .ToList();

The SQL query generated by this is:

SELECT 
    [Extent1].[OwnerID] AS [OwnerID], 
    [Extent1].[OwnerName] AS [OwnerName]
    FROM [dbo].[Owners] AS [Extent1]
    WHERE  NOT EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[Pets] AS [Extent2]
        WHERE ([Extent1].[OwnerID] = [Extent2].[OwnerID]) AND (N'Inactive' = [Extent2].[Status])
    )

Or to remove the clutter:

SELECT 
    OwnerID,
    OwnerName
    FROM Owners o
    WHERE  NOT EXISTS (SELECT 
        1
        FROM Pets p
        WHERE (o.OwnerID = p.OwnerID AND p.Status = 'Inactive')
    )

---

If you have more values for Status, you could use (Entity Framework):

owners = context.Owners
    .Where(o => o.Pets.Any(p => p.Status == "Active") || !o.Pets.Any())
    .Where(o => !o.Pets.Any(p => p.Status == "Inactive" /* || p.Status == "Lost" and any other values */))
    .ToList();

which would generate the SQL query:

SELECT 
    [Extent1].[OwnerID] AS [OwnerID], 
    [Extent1].[OwnerName] AS [OwnerName]
    FROM [dbo].[Owners] AS [Extent1]
    WHERE (( EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[Pets] AS [Extent2]
        WHERE ([Extent1].[OwnerID] = [Extent2].[OwnerID]) AND (N'Active' = [Extent2].[Status])
    )) OR ( NOT EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[Pets] AS [Extent3]
        WHERE [Extent1].[OwnerID] = [Extent3].[OwnerID]
    ))) AND ( NOT EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[Pets] AS [Extent4]
        WHERE ([Extent1].[OwnerID] = [Extent4].[OwnerID]) AND (N'Inactive' = [Extent4].[Status])
    ))

You'd want to test that for performance and there may well be better ways, but it gives the desired result. It does assume you have foreign key/navigation property though.

Answer 2

Try the following query:

select o.*
from dbo.owner o
where not exists(
  select *
  from dbo.pet p
  where p.ownerid=o.ownerid and
    p.status='Inactive'
);

Answer 3

SELECT  OwnerID, OwnerName 
FROM Owner 
WHERE OwnerID NOT IN (
SELECT OwnerID from Pet
WHERE Status='Inactive'

This simple query will do the thing.

OwnerId        OwnerName 
2              Marie
3              Alex

And if you want to select owner with atleast one ACTIVE or NO PET then use the below query.

SELECT o.OwnerID o.OwnerName
FROM Owner o 
LEFT JOIN Pet p 
ON o.OwnerID= p.OwnerID 
AND (p.Status='Active' 
OR p.OwnerID is NULL)

OwnerId        OwnerName
1              John
2              Marie
3              Alex

This query will return OWNER name until that Owner's all pets are INACTIVE

Now for another case..

If there is a chance for your table to have OwnerId as NULL in Pets Table. Kindly use the below Query. (Mysql)

SELECT OwnerID, OwnerName 
FROM Owner 
WHERE OwnerID NOT IN (
   SELECT IFNULL(OwnerID,0) from Pet
   WHERE Status='Inactive');

ADDED IFNULL() in subquery.

SQLFIDDLE