on the basis of country I want list of suppliers that has more than one suppliers and from such suppliers I want those who has product(can be any one) price less than 20.
suppliers table :
supplier_name id country
A 1 germany
B 2 london
C 3 london
D 4 germany
Products table :
products price id
onion 30 1
tomato 20 1
potato 3 1
pulses 60 2
rice 18 2
spice 100 2
jacket 300 3
fruits 8 4
My sql query for this question :
select id,
supplier_name,
count(country) as sd ,
( select distinct s.id
from suppliers s
inner join products p on s.id=p.id
where price<20) as d
from suppliers
group by country
having sd > 1;
This is my query but column d and sd is printed as extra column which I don't need
can anyone help me to write the better non-correlated query with inner join ?
I want list of suppliers where a country has more than one suppliers and from such suppliers I want those who has product(can be any one) price less than 20.
Look at the above suppliers table where germany has two suppliers A, D which is more than one and from such suppliers I want those who has product(can be any one) price less than 20.
so the output will be :
suppliers_name id
A 1
B 2
D 4
Your query fails miserably in Access which is only query engine I have. Consider:
SELECT id, supplier_name
FROM Suppliers INNER JOIN (
SELECT country, Count(ID) AS CntID
FROM Suppliers GROUP BY country) AS C
ON Suppliers.country = c.country
WHERE CntID>1 AND id IN (SELECT supID FROM Products WHERE price<20)
ORDER BY ID;
Or
SELECT DISTINCT Suppliers.id, Suppliers.supplier_name
FROM Products
INNER JOIN (Suppliers INNER JOIN (SELECT country, Count(ID) AS CntID
FROM Suppliers GROUP BY country) AS C
ON Suppliers.country = C.country)
ON Products.supID = Suppliers.ID
WHERE C.[CntID]>1 AND Products.price<20;
I renamed id in Products to supID.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments