Re: Reporting on Multiple Detail Tables

In <jdpiercC9I1ox.7wv@netcom.com> jdpierc@netcom.com (Jerry D. Pierce) writes: >OK. Here's the picture. We're running Informix 4.10.UD2 and >have a database which contains 3 tables. >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 >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??? Maybe!: select name, master.ss_number, foo_date, moo_date from master, outer detail1, outer detail2 where master.ss_number = detail1.ss_number and master.ss_number = detail2.ss_number order by 2 will probably improve things, but it probably still won't handle the situation where 0 < # foo_date rows <> # moo_date rows > 0 the way you would like it to. I would resolve this by logic in my PRINT EVERY ROW block saying "if this detail row is same as previous detail row then don't print it". Similarly, if I didn't want to see the masters with no details I would resolve this in my PRINT EVERY ROW or do: select name, master.ss_number, foo_date, moo_date from master, outer detail1, outer detail2 where master.ss_number = detail1.ss_number and master.ss_number = detail2.ss_number order by 2 into temp a; select * from a where not (foo_date is null and moo_date is null) order by ss_number; (for reasons that I don't understand, testing for nulls in the outer join's where clause, doesn't work. Is this a bug, Informix gurus?) If (as I suspect) you have information about the relationship between # of foo_dates versus # of moo_dates that you have not imparted, then you could probably simplify this approach. graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.