Subquery in a where clause - SQL Query

Question

I have the following data:

Supplier Name     Supplier City     Supplier Country      Spend
Supplier A        New York          US                    500,000
Supplier A        Dublin            Ireland               300,000
Supplier B        London            UK                    400,000
Supplier C        Austin            US                    300,500
Supplier C        London            UK                    200,000
Supplier D        Barcelona         Spain                 500,000
Supplier D        Paris             France                650,000

I am looking to get the data only for the regional Europe suppliers and not the one that also operate in the US. So from the above data I only need Supplier B and D since A and C also operate in the US (New York and Austin respectively).

I am trying something like:

select
supplier_name,
  supplier_city, supplier_country, spend
from
supplier_detail
where
supplier_name IN (select distinct supplier_name, supplier_city, supplier_country from supplier_detail where supplier_country<>'US')

The problem is that this still returns Supplier A (for Newyork) and Supplier C (for Austin).

Can anyone help?

Thanks. A

Aside from the good answer provided by Gosfly, you may want to normalize your data. What happens if "Supplier A" changes their legal name. Are you going to go through each record and update? Similarly for possible City which would have a country as well. Just something to consider for future. — DRapp
– DRapp, Commented Nov 18, 2019 at 14:36

Tim Biegeleisen · Accepted Answer · 2019-11-18 12:50:30Z

1

I would use aggregation here:

SELECT sd1.supplier_name, sd1.supplier_city, sd1.supplier_country, sd1.spend
FROM supplier_detail sd1
INNER JOIN
(
    SELECT supplier_name
    FROM supplier_detail
    GROUP BY supplier_name
    HAVING COUNT(CASE WHEN supplier_country = 'US' THEN 1 END) = 0
) sd2
    ON sd1.supplier_name = sd2.supplier_name

Demo

answered Nov 18, 2019 at 12:50

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

forpas · Accepted Answer · 2019-11-18 12:50:49Z

1

With not exists:

select supplier_name, supplier_city, supplier_country, spend
from supplier_detail s
where not exists (
  select 1 from supplier_detail
  where supplier_name = s.supplier_name and supplier_country = 'US'
)

assuming that all the other countries are from Europe.
Or:

select supplier_name, supplier_city, supplier_country, spend
from supplier_detail 
where supplier_name not in (
  select supplier_name from supplier_detail
  where supplier_country = 'US'
)

answered Nov 18, 2019 at 12:50

forpas

165k10 gold badges51 silver badges85 bronze badges

1 Comment

forpas Over a year ago

@AbhishekDahiya then don't forget to accept th eanswer.

Gordon Linoff · Accepted Answer · 2019-11-18 12:48:15Z

0

You can use not exists:

select sd.*
from supplier_detail sd
where not exists (select sd2.*
                  from supplier_detail sd2
                  where sd2.supplier_name = sd.supplier_name and
                        sd2.supplier_country not in ('Ireland', 'UK', 'Spain', 'France', . . .)
                 );

You can also use:

where not exists (select sd2.*
                  from supplier_detail sd2
                  where sd2.supplier_name = sd.supplier_name and
                        sd2.supplier_country not in ('US')
                 );

I'm not sure if it is easier to list out the European countries or to list out the rest of the world.

answered Nov 18, 2019 at 12:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Gosfly · Accepted Answer · 2019-11-18 14:16:07Z

0

You should inverse your condition like this :

SELECT
  supplier_name,
  supplier_city, 
  supplier_country, 
  spend
FROM
  supplier_detail
WHERE
  supplier_name NOT IN (
    SELECT DISTINCT supplier_name 
    FROM supplier_detail 
    WHERE supplier_country = 'US')

See example HERE

edited Nov 18, 2019 at 14:16

answered Nov 18, 2019 at 12:51

Gosfly

1,2982 gold badges8 silver badges16 bronze badges

Collectives™ on Stack Overflow

Subquery in a where clause - SQL Query

4 Answers 4

Demo

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related