SQL query on two tables using two column as keys

I started studying SQL (not MySQL as it seems to be different in some parts) and I encountered this problem that i could not figure out how to solve

I have the following tables:

CREATE TABLE products(
    product char(30),
    color   char(30)
);

INSERT INTO products (product, color)
VALUES 
("table","brown"),
("chair","brown"),
("shelf","brown"),
("table","black"),
("chair","white");

CREATE TABLE suppliers(
    supplier char(30),
    product char(30),
    color   char(30)
);

INSERT INTO suppliers (supplier, product, color)
VALUES 
("s1","chair","brown"),
("s1","door","brown"),
("s1","table","brown"),
("s1","table","black"),
("s1","shelf","brown"),
("s2","chair","brown"),
("s3","table","brown"),
("s3","table","black"),
("s3","chair","brown"),
("s3","chair","white"),
("s3","shelf","white");

I need to find using SQL the suppliers and the number of brown products which they can provide to the store and the store sells them, ordered by the count as ascending (brown products in products table). I started out with sub queries and something like this:

select supplier,COUNT(pb.product)
from (select product from products where color = "brown") as pb, (select supplier, product from suppliers where color = "brown") as sb
where pb.product == sb.product;

but I cannot figure it out, please help.

The results on these tables need to be:

supplier| no_of_products
-------------------------
   s2   |   1
   s3   |   2
   s1   |   3

Answer

At the end I needed the following:

select supplier, COUNT(s.product)
From suppliers s, products p
WHERE s.product = p.product and s.color = "brown" and p.color = "brown"
GROUP by supplier
order by COUNT(s.product);

Advertisement

Answer