In your query use joins & alias's to query the location table 3 times. This will create empty place holder for the missing info, but will have the structure your client wants.
Rick Q [email protected] On Tue, Mar 1, 2011 at 2:30 PM, Ken Kixmoeller (ProFox) < [email protected]> wrote: > Hi - - -- > > A client wants to look at the data the way that they always have: > i.e.: as messed up as it used to be. (Before I normalized it). (Feel > free to insert "interesting" client stories here.) > > The business situation is: A person with an order. The billing party > could be the person himself, another person in the same organization > (same or different address), or a person in a different organization. > At different times, the same person could have a different billing > party. (Example: Buying something through his company one day, on his > own another day.) > > I have 4 tables in the mix: > > Person > Loc_Type (Work, Home, or Billing + other stuff) > Location (for an organization i.e.: most have more than one location) > Organization (including his household) > > So a typical query on this stuff will yield: > > Billy Smith Home Smith House 123 Main Street ... > Billy Smith Work General Mills 3456 Commercial Avenue ... > Billy Smith Billing General Mills 999 Admin Bldg, 222 Commercial > Avenue > > The client wants: > first last home_name home_address work_name > work_address bill_name bill_addr > Billy Smith Smith House 123 Main Street General Mills 3456 > Commercial Av General Mills 999 Admin Bldg, 222 Commercial Av > > FWIW: Of course, we don't necessarily have all 3 addresses for a given > individual. maybe, 1, 2, or 3. > > Any SQL way to do that? I can't think of one... > > Ken > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
