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]
