using where exists instead of join-CodePudding

using sql server the tables are

| products            |  | sales             |  | distributors      |
|:-------------------:|  |:-----------------:|  |:-----------------:| 
| prod_id | prod_name |  | prod_id | dist_id |  | dist_id | country |
| ------- | --------- |  | ------- | ------- |  | ------- | ------- |
| P1010   | Name 1    |  | P1010   | D505    |  | D505    | AU      |
| Second  | row       |  | Second  | row     |  | Second  | row     |

Edit: Need to find all products sold, excluding the ones that are also sold in AU (products not sold anywhere should not be included).

(Original: need to find all products NOT sold in Australia (AU))

Edit: original request i was given was phrased incorrectly i had gotten the 'correct' result for the request with the 'not exists' query

select 
p.prod_id, p.prod_name from products p
where not exists (
    select s.prod_id
    from sales s, distributors d
    where p.prod_id=s.prod_id
    and d.dist_id=s.dist_id
    and country ='au')

(similar to the one Stu posted) but since it didn't match the supplied expected result i didn't realize the problem was the request not the query. But this does not give the result excluding products not sold anywhere.

i managed it using join

select 
p.prod_id, p.prod_name from products p
    join sales s on s.prod_id = p.prod_id
    join distributors d on d.dist_id = s.dist_id
    where d.country not in ('au')
    and s.prod_id not in (
    Select s.prod_id from sales s
        join distributors d on d.dist_id = s.dist_id
        where d.country = 'au')
    Group by p.prod_id, p.prod_name

but the requirement is: (you MUST use EXISTS )

i tried these 2 options:

p.prod_id, p.prod_name from products p
    where exists (
    select s.prod_id
    from sales s, distributors d
        where p.prod_id=s.prod_id
        and s.dist_id=d.dist_id
        and country <>'au'
        and s.prod_id not in (
        Select s.prod_id from sales s
            where d.dist_id = s.dist_id and country = 'au'))
Group by t.title_id, title

and

p.prod_id, p.prod_name from products p
    where exists (
    select s.prod_id
    from sales s, distributors d
        where p.prod_id=s.prod_id
        and s.dist_id=d.dist_id
        and country <>'au'
        and not exists (
        Select s.prod_id from sales s
            where d.dist_id = s.dist_id and country = 'au'))
Group by t.title_id, title

but it does not exclude the products sold both in AU and elsewhere

this part

and s.prod_id not in (
    Select s.prod_id from sales s
        where d.dist_id = s.dist_id and country = 'au')

can be taken out or left it the result is the same

it does work if constructed with a join in the last subquery:

 p.prod_id, p.prod_name from products p
        where exists (
        select s.prod_id
        from sales s, distributors d
            where p.prod_id=s.prod_id
            and s.dist_id=d.dist_id
            and country <>'au'
            and s.prod_id not in (
            Select s.prod_id from sales s
                join distributors d on d.dist_id = s.dist_id
                where d.country = 'au'))
        Group by p.prod_id, p.prod_name

Any way to eliminate that last join and replace with exists or in? or is there a better way to construct the whole thing with exists.

CodePudding user response：

I would suggest all you need is something like the following - you even tagged not-exists

select p.prod_id, p.prod_name 
from products p
where not exists (
    select * 
    from sales s 
    join distributors d on d.dist_id = s.dist_id
    where s.prod_id = p.prod_id
)

CodePudding user response：

I think the hang-up here is "... excluding the ones ..." Lets first confirm and simplify all products sold

select distinct prod_id from sales

Easy enough... Now, confirm simplfied query of all products sold in AU

select distinct s.prod_id from sales s join distributors d on s.dist_id = d.dist_id AND d.country = 'AU'

So, as one product COULD be available from different distributors such that product P1010 available by 'AU', 'US', 'CN', 'IN', etc you would want to EXCLUDE product P1010. But now you could have product X2020 and only available by 'CN' and 'IN'. You would want THAT in your final output correct?' So the short is the net of the two

select distinct 
      s1.prod_id 
   from 
      sales s1
   where
      s1.prod_id NOT EXISTS ( select distinct 
                                    s.prod_id
                                 from
                                    sales s
                                       join distributors d
                                          on s.dist_id = d.dist_id
                                          AND d.country = 'AU' )

Finally, if you want all the products (such as the product X2020), and all the suppliers you can get those products from, just add that to the outer query qualifier and join again to the distributors IF you need the respective country

select distinct 
      s1.prod_id,
      s1.dist_id,
      d1.country
   from 
      sales s1
         join distributors d1
            on s1.dist_id = d1.dist_id
   where
      s1.prod_id NOT EXISTS ( select distinct 
                                    s.prod_id
                                 from
                                    sales s
                                       join distributors d
                                          on s.dist_id = d.dist_id
                                          AND d.country = 'AU' )