On Fri, 20 Dec 2002 04:18:42 -0600, Scott Lamb <slamb@slamb.org>
wrote:
>that's also assuming d.company_name is distinct in depot.
No, it's not.
CREATE TABLE depot (depot_id INT, company_name TEXT);
INSERT INTO depot VALUES (1, 'SOME');
INSERT INTO depot VALUES (2, 'ANY');
INSERT INTO depot VALUES (3, 'SOME');
CREATE TABLE bom (bom_id int, depot_id int);
INSERT INTO bom VALUES (11, 1);
INSERT INTO bom VALUES (12, 2);
INSERT INTO bom VALUES (13, 3);
INSERT INTO bom VALUES (14, 4);
INSERT INTO bom VALUES (21, 1);
INSERT INTO bom VALUES (22, 2);
INSERT INTO bom VALUES (23, 3);
SELECT bom.*
FROM bom, depot d
WHERE bom.depot_id = d.depot_id
AND d.company_name = 'SOME';
SELECT bom.*
FROM bom INNER JOIN depot d ON bom.depot_id=d.depot_id
WHERE d.company_name ='SOME';
> Otherwise
>you'll get repeated bom.*s for each d it matches.
No repeatd boms here:
bom_id | depot_id
--------+----------
11 | 1
13 | 3
21 | 1
23 | 3
(4 rows)
The point is: there can be many depots matching company_name='SOME',
but there's at most one matching depot for each bom.
Servus
Manfred