CODEWITHSUNDEEP
sqlpostgresql-9.2
Popito
Popito

Reputation: 3

Confused with sql query

I have the following tables:

Customer(login varchar, town varchar)
Orders (Ordno int , login varchar) >>login Fk from Customer
combination (Id int, ordno int ,Product_Id int) >>ordno fk from orders

I need to show the products that have been sold in ALL the cities.

Example:

Insert into Customer (log1 , NY) (log2, NY) (log3, London)
Insert into Orders (1,log1) (2,log1) (3,log3) 
Insert into combination (1,1,1) (2,2,2) (3,3,1)

if the available cities are only NY and London, then the only product that must be the result of the query is product 1

Upvotes: 0

Views: 60

Answers (3)

user170442
user170442

Reputation:

Assuming Products table looks like this:

Products (Product_Id int, Name)

You need to join stuff all the way down (or up) to customer... 

SELECT p.Name, c.town
FROM Products p
  INNER JOIN Combination comb ON comb.Product_Id=p.Product_Id
  INNER JOIN Orders o ON o.Ordno=comb.ordno
  INNER JOIN Customer cust ON cust.login=o.login
GROUP BY p.Name, c.town

Upvotes: 0

John Woo
John Woo

Reputation: 263723

SELECT  a.ProductID
FROM    Combination a
        INNER JOIN Orders b
            ON a.OrdNo = b.OrdNo
        INNER JOIN Customer c
            ON b.Login = c.LogIn
GROUP   BY a.ProductID
HAVING  COUNT(DISTINCT a.ID) = (SELECT COUNT(DISTINCT town) FROM Customer)

Upvotes: 2

SpaceApple
SpaceApple

Reputation: 1327

Not sure what exactly you are trying to do here.

SELECT c.Town, cc.Product_Id FROM from Customer c
JOIN Orders o ON c.login = o.login
JOIN Combination cc ON o.Ordno = cc.ordNo
GROUP BY c.town

This will group the towns together and show you the Product_Id

You still need a Product table to display the product table.

This query excludes the product table

Upvotes: 0

Related Questions