Sql server запрашивает соединение двух таблиц для получения требуемого результирующего набора

У меня есть две таблицы Customer-items и Available-locations мне нужно получить расположение, где все элементы в customer-items доступны.

Клиент-детали

ItemID    ItemName
   1        item1      
   2        item2 
   3        item3      

Доступные места

LocationID    ItemID      AvailableForPickup
   20           1             1
   20           2             1
   20           3             0
   21           1             1
   21           2             1
   21           3             1

о простом внутреннем соединении e.g

 SELECT * FROM Customer-items
 INNER JOIN Available-locations
 ON Customer-items.ItemID = Available-locations.ItemsID AND AvailableForPicup = 1

это исключит пункт 3 из доступных местоположений для идентификатора местоположения 20, но вернет два других элемента для идентификатора местоположения 20 и все элементы для местоположения 21.

мне нужно получить результирующий набор как; исключить все элементы для размещения, если какой-либо из элементов недоступен.

Доступно-locations_CustomerItems

LocationID    ItemID      AvailableForPickup
   21           1             1
   21           2             1
   21           3             1

5 ответов

  1. SELECT * FROM Customer-items
    INNER JOIN Available-locations
    ON Customer-items.ItemID = Available-locations.ItemsID AND locationId
    NOT IN (select locationID FROM Available-locations WHERE AvailableForPicup = 0)
    
  2. Вы можете использовать NOT INв следующих:

     SELECT l.* 
     FROM [Customer-items] AS i 
     INNER JOIN [Available-locations] AS l ON i.ItemID = l.ItemID
     WHERE l.LocationID NOT IN (SELECT LocationID FROM [Available-locations] WHERE AvailableForPicup = 0) 
    

    Обратите внимание, что -это недоступно в именах таблиц. Вы должны использовать скобки []на названиях таблиц, таких как [customer-items]. Также используйте псевдонимы в таблицах, чтобы сделать его более читаемым.

    ВЫХОД

    LocationId  ItemId  AvailableForPickup
    21          1       1
    21          2       1
    21          3       1
    
  3. select * from Customer-items
    join Available-locations
    on Customer-items.ItemID = Available-locations.ItemsID
    where Available-locations.LocationID in 
    (select LocationID from Available-locations where AvailableForPickup = 1)
    
  4. Пожалуйста, используйте код ниже. Он отлично работает с SQL Server 2012.

    DECLARE @Customer_Items TABLE (ItemsID int,ItemName VARCHAR(10))
    DECLARE @Available_Locations TABLE (LocationID int,ItemsID int,AvailableForPickup int)
    INSERT INTO @Customer_Items (ItemsID,ItemName)
    VALUES
    (1,'item1'),
    (2,'item2'),
    (3,'item3')
    INSERT @Available_Locations
    (LocationID,ItemsID,    AvailableForPickup)
    VALUES
    (20,1,1),
    (20,2,1),
    (20,3,0),
    (21,1,1),
    (21,2,1),
    (21,3,1)
    
    SELECT LocationID,al.ItemsID,AvailableForPickup FROM @Customer_Items ci
     INNER JOIN @Available_Locations al
     ON ci.ItemsID = al.ItemsID AND al.AvailableForPickup = 1
     WHERE al.LocationID NOT IN (SELECT al2.LocationID FROM @Available_Locations al2 WHERE al2.AvailableForPickup =0)