Hello.  I'd appreciate any help from you sql gurus on this problem:

I have tables Foo, Bar, and FooBarJoin, which is your typical join
table for a m-n relationship.

I need to find the unique rows in Foo, where the uniqueness
consideration includes the relationships with Bar.  E.g.,

So Foos 1 and 2 are equal, sharing both the attribute x and the
relationship to Bar 2.  Foo 3 is unique even though it shares x=a, and
is joined to Bar 2, because it's also joined to Bar 5.

So what's the best way to find the unique rows?

Thanks,

Jim Hughes

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.  Is this what you meant, if you were consistent about
names, given constraints, etc.??

CREATE TABLE Foo
(foo_id INTEGER NOT NULL PRIMARY KEY,
x CHAR(1) NOT NULL);

CREATE TABLE Foobar
(foo_id INTEGER NOT NULL
   REFERENCES Foo(foo_id),
bar INTEGER NOT NULL);

INSERT INTO Foo VALUES (1, 'a');
INSERT INTO Foo VALUES (2, 'a');
INSERT INTO Foo VALUES (3, 'a');
INSERT INTO Foo VALUES (4,' b');

INSERT INTO Foobar VALUES (1, 2);
INSERT INTO Foobar VALUES (2, 2);
INSERT INTO Foobar VALUES (3, 2);
INSERT INTO Foobar VALUES (3, 5);

SELECT Foo.foo_id, Foo.x, MAX(bar)
  FROM Foo, Foobar
WHERE Foo.foo_id = Foobar.foo_id
GROUP BY Foo.foo_id, Foo.x
HAVING MIN(bar) = MAX(bar);

On 16 Sep 2005 17:46:44 -0700, jeem.hug***@gmail.com wrote:

Show quoteHide quoteHi Jim,

Here is one possible way, using an extended version of the exact
relational division query:

SELECT     f1.id, f2.id
FROM       Foo            AS  f1
INNER JOIN Foo            AS  f2
      ON   f2.id           >  f1.id
      AND  f2.x            =  f1.x
LEFT  JOIN FooBarJoin     AS  fbj1
      ON   fbj1.fid        =  f1.id
LEFT  JOIN FooBarJoin     AS  fbj2
      ON   fbj2.fid        =  f2.id
      AND  fbj2.bid        =  fbj1.bid
GROUP BY   f1.id, f2.id
HAVING     COUNT(fbj1.fid) =  COUNT(fbj2.fid)
AND        COUNT(fbj1.fid) = (SELECT COUNT(*)
                              FROM   FooBarJoin AS  fbj3
                              WHERE  fbj3.fid    =  f2.id)

Best, Hugo

Update Lock
select variable in from statement
Test for numerics?
Date/String format
PRIMARY KEY