SQL query to display products that have been sold in one country and not another

Question

Display the supplier names that have products sold to Mexico but not Spain

SELECT s.CompanyName 
FROM  OrderSupplier.Suppliers s
WHERE Country <> 'Spain' 
(SELECT p.Id 
FROM OrderSupplier.Suppliers s
JOIN OrderSupplier.Products p ON s.Id = p.SupplierId 
JOIN OrderSupplier.OrderItem oi ON p.Id = oi.ProductId 
JOIN OrderSupplier.Orders o ON oi.OrderId = o.Id 
JOIN OrderSupplier.Customers c ON o.CustomerId = c.Id
WHERE c.country = 'Mexico')

enter image description here

What is your question here? What's wrong with the attempt you've made? — Thom A, Aug 23 '20 at 13:44
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Aug 24 '20 at 02:06
Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. (This question should be all text.) — philipxy, Aug 24 '20 at 02:07

score 2 · Answer 1 · answered Aug 23 '20 at 13:53

2

One option uses a series of joins to bring all customers of each supplier, then aggregation:

SELECT s.Id 
FROM OrderSupplier.Suppliers s
INNER JOIN OrderSupplier.Products p ON s.Id = p.SupplierId 
INNER JOIN OrderSupplier.OrderItem oi ON p.Id = oi.ProductId 
INNER JOIN OrderSupplier.Orders o ON oi.OrderId = o.Id 
INNER JOIN OrderSupplier.Customers c ON o.CustomerId = c.Id
WHERE c.country IN ('Mexico', 'Spain')
GROUP BY s.Id
HAVING MIN(c.country) = MAX(c.country) and MIN(c.country) = 'Mexico'

The WHERE clause filters on customers in Spain or Mexico. The HAVING clause then ensures that only Mexico was found.

answered Aug 23 '20 at 13:53

GMB

216,147
25
84
135

Hi there. Thank you so much for this answer! Can you please explain the use of MIN and MAX? I am a new learner of SQL and dont quite understand that part – Aug 23 '20 at 14:28
Hey thank you for the input really appreciate it. Can you please delete your previous comment with my name if thats ok? As this is for a uni assignment and I dont think I was meant to post here.... – Aug 24 '20 at 02:57

score 0 · Answer 2 · answered Aug 23 '20 at 15:03

Something like this

;with
mex_cte as (
    select distinct s.id supplier_id
    from OrderSupplier.Suppliers s
         join OrderSupplier.Products p ON s.Id = p.SupplierId 
         JOIN OrderSupplier.OrderItem oi ON p.Id = oi.ProductId 
         JOIN OrderSupplier.Orders o ON oi.OrderId = o.Id 
         JOIN OrderSupplier.Customers c ON o.CustomerId = c.Id
    WHERE c.country = 'Mexico'),
sp_cte as (
    select distinct s.id supplier_id
    from OrderSupplier.Suppliers s
         join OrderSupplier.Products p ON s.Id = p.SupplierId 
         JOIN OrderSupplier.OrderItem oi ON p.Id = oi.ProductId 
         JOIN OrderSupplier.Orders o ON oi.OrderId = o.Id 
         JOIN OrderSupplier.Customers c ON o.CustomerId = c.Id
    WHERE c.country = 'Spain')
select * from mex_cte
except
select * from sp_cte;

this answer seems much more understandable than the answer from @GMB, although technically you don't need to put each sql into a cte. also I'd recommend against using distinct for performance reasons. — xzilla, Aug 23 '20 at 15:36
I actually understand this one better! How would you do it without WITH or cte though? — , Aug 24 '20 at 02:50

score 0 · Answer 3 · answered Dec 02 '21 at 12:37

SELECT 
P.ProductName, 
S.Country
FROM Products P
INNER JOIN Suppliers S on S.Id = P.SupplierId
where S.Country = 'USA'
and P.ProductName NOT IN
(
Select P.ProductName 
FROM Products P
INNER JOIN Suppliers S on S.Id = P.SupplierId
where S.Country = 'France'
)
Group by P.ProductName, S.Country

This gives you the products that were sold in USA but not France.

score -3 · Answer 4 · answered Aug 23 '20 at 13:45

-3

You should provide sample data and expected result but based on what you have provided you can achieve the result by using NOT EXISTS

answered Aug 23 '20 at 13:45

Atif

2,011
9
23

How do I do that? – Aug 23 '20 at 14:53
Provide a sample data or Fiddle – Atif Aug 23 '20 at 15:03

SQL query to display products that have been sold in one country and not another

4 Answers4