Re: Reporting on Multiple Detail Tables

jdpierc@netcom.com (Jerry D. Pierce) writes: >I have a master table and 2 detail tables which are joined >together via a primary key (social security number). How can >I get a report for every row in both detail tables for their >corresponding entry in the master table WITHOUT reporting on >every row in detail table 1 for each row in detail table 2. >(Which results in massive amounts of duplicate data appearing) >Master table: > name, ss_number >Detail table 1: > ss_number, foo_date >Detail table 2: > ss_number, moo_date I think this will work: select name, master.ss_number, foo_date from master, detail1 where master.ss_number = detail1.ss_number UNION ALL select name, master.ss_number, moo_date from master, detail2 where master.ss_number = detail2.ss_number order by 2 -- if you need to know which side of the union any row comes from: select name, master.ss_number, "D1", foo_date from master, detail1 where master.ss_number = detail1.ss_number UNION ALL select name, master.ss_number, "D1", moo_date from master, detail2 where master.ss_number = detail2.ss_number order by 2 -- if you really do want to elimate rows in detail1 not in detail2: select name, master.ss_number, "D1", foo_date from master, detail1 where master.ss_number = detail1.ss_number and detail1.ss_number in (select unique ss_number from detail2 where ss_number = master.ss_number) UNION ALL select name, master.ss_number, "D1", moo_date from master, detail2 where master.ss_number = detail2.ss_number and detail2.ss_number in (select unique ss_number from detail1 where ss_number = master.ss_number) order by 2 -- Can you tell I just finished teaching an SQL class? ==================== Dennis Pimple CSE, Informix Denver >select name, master.ss_number, foo_date, moo_date > from master, detail1, detail2 >where master.ss_number = detail1.ss_number >and master.ss_number = detail2.ss_number >order by 2 >SAMPLE OUTPUT: > Henry 555-55-5555 01/01/90 05/05/91 > 01/01/90 08/18/92 > 01/01/90 12/19/92 >There has GOT to be a way around this.... >Isn't this do-able with the standard engine and JUST ISQL??? > Jerry D. Pierce > jdpierc@netcom.com