Self Join - To use a repeat region in recordset
I'm trying to do a self join. I need to get the columns oppo1 and oppo2 on the same row. This way I can use the repeat region in the recordset. The inner join I'm using gets me close, however, I keep getting duplicates. I've tried using DISTINCT and GROUP BY. Neither can get me what I need since the gameID and teamID are different. Here is what I have. Any suggestion?
Thx
MIke
SELECT DISTINCT E.teamID,E.oppo1, ME.oppo2 AS Opponent2
FROM games E
LEFT JOIN games ME
ON ME.teamID = E.teamID
Here is the output. I need to get rid of the duplicates
164 219 Tates Creek
169 219 Tates Creek Paris Western
165 220 Harrison County
168 220 Harrison County Scott County
166 218 Trigg County
167 218 Trigg County Boston Celtics