Path: utzoo!attcan!uunet!acd4!mjt
From: mjt@acd4.UUCP ( Mark Teegarden      )
Newsgroups: comp.databases
Subject: A more difficult SQL question involving a union
Summary: This unioned select returns the same record twice.
Keywords: SQL, select, union, ORACLE
Message-ID: <1990Jun13.174337.12111@acd4.UUCP>
Date: 13 Jun 90 17:43:37 GMT
Sender: mjt@acd4
Reply-To: mjt@DEV2.UUCP ( Mark Teegarden      )
Organization: Applied Computing Devices, Inc., Terre Haute, IN
Lines: 54

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


The view is built to retrieve a player and his opponent no matter if
the player is playerA or playerB. If playerB is the selected player,
the record will be selected with player B displayed with his
respective handicap as the first player (Player) and player A
displayed with his respective handicap as the second player
(Opponent). The actual view that I was working with was more
complicated and used several tables instead of just one. The view can
be approximated as this for our sample table:

Create view Match as
select State, PlayerA Player, handicapA hcap1, PlayerB Opponent, 
       handicapB hcap2
 from games
union
select State, PlayerB Player, handicapB hcap1, PlayerA Opponent, 
       handicapA hcap2
 from games;

If I used the view to select all the players and their opponents from
Illinois, the results would look like this:

select * from match where state = 'IL';

State   Player   hcap1   Opponent  hcap2
-----   ------   -----   --------  -----
IL      3        1       9         3
IL      9        3       3         1

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?

-- 
                     --                 --
Mark Teegarden				2 wire	(812)232-6051
Applied Computing Devices		4 wire  uunet!acd4!mjt 
100 N. Campus Dr.  Aleph Pk.		        mjt@acd4