Writing an SQL query that shows suppliers of only best shops in specific state
I am trying to write an SQL query that will show me the suppliers of ONLY our best shops in a specific state. I DO NOT want the suppler to appear if he supples goods to other shops that are not the best. (I have already written the query to show me the best shops). How do I exclude a supplier if they also supply shops that aren't in the best shops query?
First let's do the suppliers of the best shops in each state, where "best" means highest sales amount. Consider the following tables --
supplier ( supplierid, suppliername ) goods ( supplierid, shopid, goodsdesc ) shop (shopid, shopname, statecd, salesamt )
We want the suppliers of the best shops by state, so that's a many-to-many join with a correlated subselect to find the highest sales by state --
select statecd, shopname, suppliername from supplier X, goods Y, shop Z where X.supplierid = Y.supplierid and Y.shopid = Z.shopid and salesamt = (select max(salesamt) from shop where statecd = Z.statecd) order by statecd, shopname, suppliername
The query above selects all suppliers who supply a shop which has the highest sales amount in the same state.
For the second part of the query, to exclude a supplier if they also supply shops that aren't in the best shops, it wasn't clear whether this meant any other shops in the same state or any other shops, period. Let's assume the latter. The above query thus needs the following additional condition --
and not exists (select 1 from goods where supplierid = X.supplierid and shopid <> Z.shopid)
The NOT EXISTS condition will be true if this supplier supplies no other shops at all, and hence this supplier will be selected in the main query.
Note that in the subselect, after the word SELECT it is necessary to select something, so conveniently choose the integer 1 instead of a table column -- it could be anything, really (including the asterisk, but that's a different subject for another day). Since a NOT EXISTS will always evaluate only true or false, the subselect doesn't need to return anything other than an indication that a row was or was not found. If a different shop is found, the subselect tries to return a row, so the NOT EXISTS is false, so the supplier is excluded from the main query.