CODEWITHSUNDEEP
sql
Alice Smith
Alice Smith

Reputation: 13

sql inner join with where exists

I have two tables Category and Device with one-to-many relationship.

Category:
- id
- name

Device:
- id
- category_id
- brand

If I want to retrieve all categories with devices I can easily join these two tables. 

SELECT c.*, d.* FROM Category c INNER JOIN Device d ON d.category_id = c.id

The problem is that I need to filter by brand as well.

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 foo.

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

I tried the following query, but seems like it's not working correctly.

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

The thing is that this WHERE EXISTS should be executed for each category.

Any help?

Upvotes: 1

Views: 9915

Answers (3)

amcdermott
amcdermott

Reputation: 1585

You could try a CTE.

;with CategoryBrand as
(
   select distinct c.id as category_id, brand
     from category c
    inner join device d on c.id = d.category_id
)
select c.*, d.*
  from CategoryBrand cb
 inner join Category c on c.id = cb.category_id
                      and cb.Brand = 'foo'
 inner join Device d on c.id = d.category_id

The CTE CategoryBrand builds a mapping of category to brand.

The SQL below gets only categories which are mapped to the desired brand and then joins from this to the devices.

Upvotes: 0

Red
Red

Reputation: 3267

You can join a table more than once; one to filter, one to get data

SELECT DISTINCT 
    cat.*
    , devData.*
FROM Device AS devFilter
    INNER JOIN Category AS cat
         ON devFilter.category_id = cat.id
    LEFT JOIN Device AS devData
         ON devData.category_id = cat.id
WHERE devFilter.Brand = 'foo'

This will be OK with a small to moderate data set. If the DISTINCT drags the performance down, I would then recommend nesting so that only 1 device is returned from devFilter before joining again.

SELECT 
    cat.*
    , dev.*
FROM
(
    SELECT DISTINCT devFilter.category_id
    FROM Device AS devFilter
    WHERE devFilter.Brand = 'foo'
) AS categoriesWithBrand
    LEFT JOIN Device AS dev
         ON dev.category_id = categoriesWithBrand.category_id
    LEFT JOIN Category AS cat
         ON cat.id = categoriesWithBrand.category_id

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need correlation :

select c.*, d.*
from Category c inner join 
     Device d
     on d.category_id = c.id
where exists (select 1 
              from Device d1 
              where d1.category_id = c.id and d1.brand = 'foo'
             );

Upvotes: 3

Related Questions