close ad
Databridge V2 with MySQLi support IS Now Available!
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Self Join - To use a repeat region in recordset

Thread began 12/27/2020 5:58 am by msbannister373603 | Last modified 12/30/2020 9:18 am by Ray Borduin | 66 views | 6 replies |

msbannister373603

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

Sign in to reply to this post

Ray BorduinWebAssist

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

Sign in to reply to this post
Did this help? Tips are appreciated...

msbannister373603

Self Join - To use a repeat region in recordset

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

Sign in to reply to this post

Ray BorduinWebAssist

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.

Sign in to reply to this post
Did this help? Tips are appreciated...

msbannister373603

Self Join - To use a repeat region in recordset

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

Sign in to reply to this post

Ray BorduinWebAssist

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.

Sign in to reply to this post
Did this help? Tips are appreciated...

msbannister373603

Reply

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

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...