Re: Reporting on Multiple Detail Tables

->From: jdpierc@netcom.com (Jerry D. Pierce) ->Subject: Reporting on Multiple Detail Tables ->Date: Thu, 1 Jul 1993 18:50:57 GMT ->Reply-To: jdpierc@netcom.com (Jerry D. Pierce) ->Organization: NETCOM On-line Communication Services (408 241-9760 guest) -> ->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??? -> -> Jerry D. Pierce -> jdpierc@netcom.com -> Jerry, Based on the info that you have given, SQL is doing exactly the right thing. Is there a correlation between foo_date and moo_date that you haven't mentioned? For example, if they are start and end dates for some interval, then they should probably be in the same table. Remember that, when you join three tables, you are creating a kind of "data cube" in some hypothetical data space. When you deal with just one ss_number, that is a "slice" thru the "cube", but that slice still has width (for all detail1 entries) and height (for all detail2 entries), so you get an output row for each detail1 / detail2 combination. We're sorta close together. If you want to pursue this by direct e-mail, instead of thru the Informix newsgroup, I'd be glad to work with you on it. Regards, ___________________________ O Alan __________________| R. Alan Popiel | H _________________________| Internet: | Martin Marietta, Tech Ops | H \\ | alan@den.mmc.com | P.O. Box 179, M/S 5422 | H \\ Std disclaimers apply.| Voice: | Denver, CO 80201-0179 USA | H ) | 303-977-9998 |___________________________| H / (But you knew that!) |_____________________) H /___________________________) H