I don't know the source of the "INTERSECT" command that keeps popping up on the list but this is a straight-forward JOIN situation if I have ever seen one.
Please read for more details: http://dev.mysql.com/doc/mysql/en/JOIN.html SELECT A.*, E.* FROM A INNER JOIN B ON A.ID = B.parentid INNER JOIN C ON A.ID = C.parentid INNER JOIN D ON A.ID = D.parentid LEFT JOIN E ON A.ID = E.parentid WHERE B.name = 'xxx' AND C.name = 'YYY' AND D.name = 'ZZZ'; Since E has optional information, it's LEFT JOINed to the group. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Sandip Bhattacharya <[EMAIL PROTECTED]> wrote on 09/09/2004 12:11:22 AM: > Background: > I have one master table A, and other supplementary tables B,C and D > such that > for every row of A there can be one or more corresponding rows in B,C,D. > There is another supplementary table E with which A has a one-to-one > relationship. > > Problem: > Given three search criteria resulting in AB, AC, and AD respectively, I need > to display results so that I get ( AB intersection AC intersection AD) and I > need to display unique rows of A on teh screen joined with corresponding row > of E. A typical multiple parameter search operation in any database with > normalized tables. > > Constraints: > Am using (sigh) mysql 3.23. No subqueries, no INTERSECT. > > > What I have tried till now: > Creating three temporary tables for AB, AC and AD respectively. Now how do I > find out the intersection of these? Stuck there. > > > The SQL with subqueries will probably be something like: > ============================================ > select A.*, E.* from A inner join E on A.id=E.parentid > where > A.id in (select distinct A.id from A inner join B on A.id=B.parentid > where B.name='XXX') > and > A.id in (select distinct A.id from A inner join C on A.id=C.parentid > where C.name='YYY') > and > A.id in (select distinct A.id from A inner join D on A.id=D.parentid > where D.name='ZZZ'); > =============================================== > > This is most probably impossible to do in one statement in mysql. > But how do I > do it at all? Any pointers willl be nice. Excuse me if I am doing something > terribly wrong. This is the first time I am getting my hands really dirty > with SQL. > > - Sandip > > > > -- > Sandip Bhattacharya * Puroga Technologies * [EMAIL PROTECTED] > Work: http://www.puroga.com * Home: http://www.sandipb.net > > PGP/GPG Signature: 51A4 6C57 4BC6 8C82 6A65 AE78 B1A1 2280 A129 0FF3 > > Woolsey-Swanson Rule: > People would rather live with a problem they cannot > solve rather than accept a solution they cannot understand. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
