title: Sql server query to join two tables to get desired result set
body: <p>I have two table <strong>Customer-items</strong> and <strong>Available-locations</strong> i need to get the location where all items in customer-items are available. </p>
  
  <p><strong>Customer-items</strong></p>
  
  <pre><code>ItemID    ItemName
     1        item1      
     2        item2 
     3        item3      
  </code></pre>
  
  <p><strong>Available-locations</strong></p>
  
  <pre><code>LocationID    ItemID      AvailableForPickup
     20           1             1
     20           2             1
     20           3             0
     21           1             1
     21           2             1
     21           3             1
  </code></pre>
  
  <p>on simple inner join e.g</p>
  
  <pre><code> SELECT * FROM Customer-items
   INNER JOIN Available-locations
   ON Customer-items.ItemID = Available-locations.ItemsID AND AvailableForPicup = 1
  </code></pre>
  
  <p>this will exclude item 3 from Available-locations for LocationID 20 but return other two items for locationID 20 and all items for location 21. </p>
  
  <p>i need to get the result set like; to exclude all items for location if any of item is not available.</p>
  
  <p><strong>Available-locations_CustomerItems</strong></p>
  
  <pre><code>LocationID    ItemID      AvailableForPickup
     21           1             1
     21           2             1
     21           3             1
  </code></pre>