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

What is the output you expect? Did you want each team listed with each other team only once? And not listed as both home and away?

Should t the results include:
Tates Creek Paris Western
and
Paris Western Tates Creek

Because they are technically different, or would you only want one listed? It might be easier to do this with two nested recordsets.

However this might work:

SELECT DISTINCT E.teamID,E.oppo1, ME.oppo2 AS Opponent2
FROM games E, games ME
WHERE E.teamID < ME.teamID

I tried it but it didn't work. Yes, the desired result is to have oppo1 and oppo2 on the same line not showing as a separate entry. Do you have info on using nested recorsets? I've never tried to use it. This is the first time I've had a huge issue using joins. The desired result is to self join oppo1 and oppo2 so they are in the same row. The join I created accomplished this but it also lists the rows by themselves. I don't need the duplicate row and it won't work with the recordset repeat region

I need to understand your database structure better to help write the correct query and make sure I'm leading you down the correct path.

What does the Games table have in it? It looks like it already has an oppo1 and oppo2... so what do you get with just select * from games?

I think if I understand the columns in the games table better then I can probably help you write the correct query.

Originally Said By: Ray Borduin

  I need to understand your database structure better to help write the correct query and make sure I'm leading you down the correct path.

What does the Games table have in it? It looks like it already has an oppo1 and oppo2... so what do you get with just select * from games?

I think if I understand the columns in the games table better then I can probably help you write the correct query.  

Yes the Games table does have oppo1 and oppo2. I figured out a workaround so I don't have to use the join. However, I did really close. I'm trying to make all the gameID be on the same row as the team. The GameIDs are separate entries into the database so I'm having trouble grouping them as a team. I need to group the gameID on the same row and show entries also where gameID is null. Here is what I'm getting using this join:

SELECT DISTINCT ME.gameID, ME.teamID, E.oppo1, ME.oppo2
FROM games ME
INNER JOIN games E
ON ME.teamID = E.teamID AND ME.oppo2 < E.oppo1

Game ID TeamID Oppo1 Oppo2
186 218 Trigg County Boston Celtics
187 219 Tates Creek Paris Western


This is the output I'm looking for. I have another team in the database. Team 217. Team 217 oppo2 is null. Therefore, the query I'm looking for should produce this:

Game ID TeamID Oppo1 Oppo2
186 218 Trigg County Boston Celtics
187 219 Tates Creek Paris Western
188 217 Maysville

I'm still confused. If you already have oppo1 and oppo2 in the table on a single row, why do you need a join at all?

Can I see what this would look like: SELECT gameID, teamID, opp1, oppo2 FROM games

If I see the database contents before the joins maybe I can understand what you need to get the output you want.

I think I figured this out. I may have been wandering the down the wrong path with trying to use a different recordset! Lol. I now have everything working correctly. Thx