[eluser]Thoer[/eluser]
@xwero: If you want to know if this could have been done without using nested selects in my from part, than the answer is absolutely. Actually my first solution for this particular problem didn't have the nested from part but it was a LOT slower. Note that I'm not saying that my solution is the best you could ever though out, but I suppose its my right to chose from the possibilities that SQL gives me.
@Derek: This bug/problem has nothing to do with the case I use it in, that's why I didn't put any examples here, but I don't want to look like a lazy-whining average Joe, so here's a test case for you, hope it helps. (It's from a Joe Coelko book, but I'm not sure of the English name of it.)
Problem: A company has many suppliers and a lot components those suppliers supply. They have a simple table with components and suppliers and want to know which suppliers supply exactly the same components. (First publicated in Database and Programming & Design mag, Dec 1993)
Table to work with:
Code:
CREATE TABLE SupplierComponent (
supplier_id CHAR(2) NOT NULL,
component_id CHAR(2) NOT NULL,
PRIMARY KEY (supplier_id, component_id)
);
There's 4 answers in the book for the problem, but here's the last one (which happens to be the fastest probably, not that it matters now)
Code:
SELECT SP1.supplier_id, SP2.supplier_id
FROM (SELECT supplier_id, component_id FROM SupplierComponent) AS SP1
INNER JOIN (SELECT supplier_id, component_id
FROM SupplierComponent) AS SP2
ON SP1.component_id = SP.component_id
AND SP1.supplier_id < SP2.supplier_id
GROUP BY SP1.supplier_id, SP2.supplier_id
HAVING (SELECT COUNT(*)
FROM SupplierComponent AS SP3
WHERE SP3.supplier_id = SP1.supplier_id)
= (SELECT COUNT(*)
FROM SupplierComponent AS SP4
WHERE SP4.component_id = SP2.component_id);
