CODEWITHSUNDEEP
sqlnorthwind
Amber Aslam
Amber Aslam

Reputation: 53

Selecting region information of product in northwind

I want to to do following in northwind database:

Display the Product Names of all products that were placed by customers in CA but I'm confused how to relate a product with customer.

Upvotes: 0

Views: 1013

Answers (4)

LogieandKy's Mom
LogieandKy's Mom

Reputation: 1

SELECT DISTINCT PRD.PRODUCTNAME 
FROM CUSTOMERS AS CUST
INNER JOIN ORDERS AS ORD
ON CUST.CUSTOMERID=ORD.CUSTOMERID
INNER JOIN [ORDER DETAILS] AS ORDDETAILS
ON ORD.ORDERID=ORDDETAILS.ORDERID
INNER JOIN PRODUCTS AS PRD
ON ORDDETAILS.PRODUCTID=PRD.PRODUCTID
WHERE CUST.REGION='CA'

To list results in ascending order by product name add this to the end

ORDER BY PRD.PRODUCTNAME ASC

Upvotes: 0

Amber Aslam
Amber Aslam

Reputation: 53

following did the job for me

Select distinct Products.ProductName From Products 
Where Products.ProductID in 
 (Select [Order Details].ProductID From [Order Details] Where [Order Details].OrderID in(select Orders.OrderID from Orders where Orders.CustomerID in(select Customers.CustomerID from Customers where Customers.Region='CA')))
 order by Products.ProductName;

Upvotes: 0

Taryn
Taryn

Reputation: 247850

Based on my recollection you will need to join the products table to the orders, orderDetails and customers table to get the list of products ordered by the customers in CA:

select distinct p.ProductName
from customers c
inner join orders o
  on c.customerId = o.customerId
inner join orderDetails od
  on o.orderId = od.orderid
inner join products p
  on od.productid = p.productid
where c.Region = 'CA'

Or you can use EXISTS:

select p.ProductName
from products p
where exists (select od.productid
              from customers c
              inner join orders o
                on c.customerId = o.customerId
              inner join orderDetails od
                on o.orderId = od.orderid 
              where c.Region = 'CA'
                and p.productid = od.productid)

See SQL Fiddle with Demo of sample of both queries.

Upvotes: 3

03Usr
03Usr

Reputation: 3435

Try this:

Select o.pdt_name From orders o 
Where o.cust_no in 
 (Select c.cust_no From customers c Where c.state = 'CA')
order by o.pdt_name

Upvotes: 0

Related Questions