title: sql inner join with where exists
body: <p>I have two tables <strong>Category</strong> and <strong>Device</strong> with one-to-many relationship.</p>
  
  <pre><code>Category:
  - id
  - name
  
  Device:
  - id
  - category_id
  - brand
  </code></pre>
  
  <p>If I want to retrieve all categories with devices I can easily join these two tables. </p>
  
  <pre><code>SELECT c.*, d.* FROM Category c INNER JOIN Device d ON d.category_id = c.id
  </code></pre>
  
  <p>The problem is that I need to filter by <code>brand</code> as well.</p>
  
  <p>Suppose, that I have one category and 3 devices for this category. 
  Now I want to get all categories with all devices where at least one out of many devices (per specific category) has a brand with value <strong>foo</strong>.</p>
  
  <p>If at least one of devices matches this brand then I need to return all devices. If there's no match then I need to return empty result (no category and no devices).</p>
  
  <p>I tried the following query, but seems like it's not working correctly.</p>
  
  <pre><code>SELECT c.*, d.*
  FROM Category c
      INNER JOIN Device d ON d.category_id = c.id
  WHERE EXISTS (SELECT * FROM Device d WHERE brand = 'foo')
  </code></pre>
  
  <p>The thing is that this <code>WHERE EXISTS</code> should be executed for each category.</p>
  
  <p>Any help?</p>