Path: utzoo!attcan!uunet!bcstec!evtprp0b!kaf9345
From: kaf9345@evtprp0b.UUCP (Kim A. Faedtke)
Newsgroups: comp.databases
Subject: Re: A more difficult SQL question involving a union
Keywords: SQL, select, union, ORACLE
Message-ID: <155@evtprp0b.UUCP>
Date: 14 Jun 90 21:53:54 GMT
References: <1990Jun13.174337.12111@acd4.UUCP>
Organization: Boeing Computer Services Everett Propulsion, Everett WA
Lines: 35

In article <1990Jun13.174337.12111@acd4.UUCP> mjt@DEV2.UUCP ( Mark Teegarden ) writes:
>Here's an interesting situation that I just ran into while doing
>program maintenance in ORACLE.  A certain select using a union will
>pull up the same record twice. I am puzzled about how to fix it
>without seriously degrading performance.  I have been working with a
>table that looks about like this. (Field names have been changed to
>protect the innocent...B-):
>
>Table: GAMES
>State   PlayerA   handicapA   PlayerB  handicapB
>-----   -------   ---------   -------  ---------
>IN      1         1           7        1
>IN      1         2           7        3
>IN      2         1           8        1
>IN      2         4           8        2
>IL      3         1           9        3
>
>
>How should I fix the view or the select to display this record only
>once and still be able to select by state alone?  Anyone have any
>suggestions?
>
>-- 


I would put just one record in the table listing a player and his opponent.  In other 
words, not all of the players would be listed in the 'Player' column, some would be listed
in the 'Opponent' column.  I would also change the columns in the table to be
'Player' and 'Opponent' instead of 'PlayerA' and 'PlayerB'.
-- 
|Kim Faedtke                       (206) 266-8653 (wk)           |
|Boeing Commercial Airplanes   ....uunet!bcstec!evtprp0b!kaf9345 |
|M/S 03-87,     P.O. Box 3707,     Seattle, WA     98124         |
|These are not the opinions of The Boeing Co.!                   |
|----------------------------------------------------------------|