Использовать внутреннее соединение, если запись существует, иначе использовать левое соединение

Ответ 1

Если ваши значения для Status являются "активными" и "неактивными", вы можете упростить свой запрос. Когда вы говорите:

Мне нужно вернуть всех владельцев, у которых есть только активные домашние животные или нет домашних животных.

Тогда это фактически переводится на:

Мне нужно вернуть всех владельцев, у которых нет неактивных домашних животных.

Тогда ваш запрос станет намного проще.

В запросе Entity Framework:

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

SQL-запрос, сгенерированный этим:

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

Или удалить беспорядок:

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

Если у вас больше значений для состояния, вы можете использовать (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();

который будет генерировать SQL-запрос:

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

Вы хотите проверить это на производительность, и могут быть лучшие способы, но он дает желаемый результат. Он предполагает, что у вас есть внешний ключ/навигационное свойство.

Ответ 2

Попробуйте следующий запрос:

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

Ответ 3

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

Этот простой запрос выполнит эту задачу.

OwnerId        OwnerName 
2              Marie
3              Alex

И если вы хотите выбрать владельца с по крайней мере одним ACTIVE или NO PET, используйте следующий запрос.

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

Этот запрос вернет имя OWNER до тех пор, пока все владельцы всех домашних животных НЕАКТИВНЫ

Теперь для другого случая.

Если у вашего стола есть шанс иметь OwnerId как NULL в таблице домашних животных. Пожалуйста, используйте нижеприведенный запрос. (Mysql)

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

ADDED IFNULL() в подзапросе.

SQLFIDDLE

Ответ 4

Интересно, что это можно сделать с помощью LEFT JOIN. Я понятия не имею, работает ли это по-разному с запросами NOT EXISTs, предложенными другими ответами.

CREATE TABLE [Owner] (
    OwnerID int PRIMARY KEY,
    OwnerName nvarchar(50)
);

INSERT INTO [Owner]
VALUES
  (1, 'John'), 
  (2, 'Marie'),
  (3, 'Alex');

CREATE TABLE Pet (
    PetID int PRIMARY KEY, 
    PetTag nvarchar(10), 
    Status nvarchar(30), 
    OwnerID int FOREIGN KEY REFERENCES [Owner](OwnerID)
);

INSERT INTO Pet
VALUES
  (1,'A341','Active', 1),  
  (2,'A342','Inactive', 1),  
  (3,'A343','Active', 2),
  (4,'A345','Active', 2);

SELECT * FROM [Owner];
SELECT * FROM Pet;

SELECT
    o.*
FROM
    [Owner] o
    LEFT JOIN Pet p
        ON o.OwnerID = p.OwnerID
        AND p.Status <> 'Active'
WHERE
    p.OwnerID IS NULL;

DROP TABLE Pet, [Owner];

Ответ 5

select DISTINCT 
     o.Id 
FROM Owner o
LEFT JOIN Pet p ON o.OwnerID= p.OwnerID
where p.Status='Active' OR p.OwnerID IS NULL

Ответ 6

SELECT DISTINCT RESULT FROM (
                            SELECT    CASE WHEN POID is NULL 
                                           THEN OID
                                           WHEN OID NOT IN (SELECT DISTINCT 
                                                            OwnerID from Pet 
                                                            WHERE Status='Inactive')
                                           THEN OID
                                      END AS RESULT
                            FROM (
                                     SELECT O.OwnerID as OID, P.OwnerID as POID
                                     FROM Owner o 
                                     LEFT JOIN Pet p 
                                     ON o.OwnerID= p.OwnerID 
                                  ) T
                            )T2 WHERE  RESULT IS NOT NULL

SQL Fiddle

Ответ 7

Интересно, что, хотя вы отметили его сущность-framework, большинство ответов не придумали упрощения, которые предлагает сущность-инфраструктура.

Между Owners и Pets существует соотношение "один ко многим". Каждый Owner имеет ноль или более Pets, каждый Pet принадлежит ровно одному Owner.

Если вы правильно настроили свои классы фреймворка для отношения one-to-many, они будут такими:

class Owner
{
    public int Id {get; set;}
    // every Owner has zero or more Pets:
    public virtual ICollection<Pet> Pets {get; set;}

    ... // other properties
}
class Pet
{
    public int Id {get; set;}
    // every Pet belongs to exactly one Owner, using foreign key:
    public int OwnerId {get; set;}
    public Owner Owner {get; set;}
}
class MyDbConnection : DbConnection
{
    public DbSet<Owner> Owners {get; set;}
    public DbSet<Pet> Pets {get; set;}
}

Этого достаточно для того, чтобы структура сущности распознала, что вы создали отношения "один ко многим". Всякий раз, когда это необходимо, инфраструктура сущности сделает для вас правильное соединение.

Мне нужно вернуть всех владельцев, у которых есть только активные домашние животные или нет домашних животных.

Это те же коллекции, что и:

Мне нужно вернуть всех владельцев, у которых нет неактивных домашних животных

(обратите внимание на себя: Владельцы без домашних животных, несомненно, не имеют Неактивных домашних животных!)

Если вы правильно настроили классы, запросы будут более читабельными. Вы можете думать о коллекциях, а не о таблицах, которые связаны друг с другом с помощью идентификаторов

using (var dbConnection = new MyDbConnection())
{
    var requestedOwners = dbConnection.Owners              // Give me all Owners
        .Where(owner => !owner.Pets.Any()                  // that have no Pets at all
          || owner.Pets.All(pet => pet.Status == Active)); // or have only active Pets
}

Entity Framework распознает, что для этого требуется соединение, и оно преобразует это в правильные внутренние соединения для вас.

Второй запрос еще проще и, вероятно, быстрее, потому что вы можете продолжить следующий Owner, как только будет найден Неактивный Pet

var ownersWithoutInactivePets = dbContext.Owners   // give me all Owners
    .Where(owner => !owner.Pets                    // that don't have
        .Any(pet => pet.Status == Inactive);       // any inactive Pets

Опять же, структура сущности сделает для вас соединение