Path: utzoo!utgpu!news-server.csri.toronto.edu!rutgers!uwm.edu!rpi!zaphod.mps.ohio-state.edu!usc!apple!voder!blia!miket
From: miket@blia.BLI.COM (Mike Tossy)
Newsgroups: comp.databases
Subject: Re: Join Contest
Message-ID: <12097@blia.BLI.COM>
Date: 18 Jul 90 21:44:24 GMT
References: <49001@seismo.CSS.GOV>
Distribution: usa
Organization: Britton Lee, Los Gatos, CA
Lines: 39

By email Jon (jkrueger@dtic.dla.mil) asked for a copy of my 15 way query or
"even of any query with more than 6 joins".  In comp.databases Jon wrote
"Extraordinary claims require extraordinary justification".

I agree.  I will try to get permission from our customer to release their
queries.  In the mean time here is a nine way join (using only five tables)
that we wrote for a proposal to the Veterans Administration (VA).


In English:
list readmissions to same bed section for patients readmitted within
14 days of previous discharge by medical district

In SQLish:
select e2.patid, f.distid
    from episode e1, episode e2, action a1, action a2, detail d1,
    detail d2, location l1, location l2, facility f
where e1.patid = e2.patid and e1.patseq + 1 = e2.patseq
    and  e2.edate - e1.ddate <= 14 
    and e2.epid = a2.epid and a2.actid=1 and a2.eactid=d2.eactid
    and e1.epid = a1.epdi and a1.actid=1 and a1.eactid=d1.eactid
    and d1.loc_code = l1.loc_code and d2.loc_code=l2.loc_code
    and l1.bed_section = l2.bed_section 
    and e2.facid = f.facid
    group by f.distid


Can't say that I'd like to make my living writing this stuff; but as
tom meyer of EDS Research (tom@edsr.eds.com) pointed out automated 
CASE tools (GUI front ends like CQT (Claris Query Tool) or Metaphor)
do generate complex queries like the one above.
--
						Teradata Corporation
Mike Tossy                                      ShareBase Division
miket@blia.bli.com                              14600 Wichester Blvd
(408) 378-7575 ext2200                          Los Gatos, CA 95030
					(Formerly: Britton Lee, Inc.)

	 These are only my opinions.