Matching either case

[Tech]

Hi there.

This is a different SQL Table/database I have.
What I want is this:

say for instance, we have 2 teams. each team has subteams.

subteams can play against each other from the same team.

I want a SQL query which will retrieve me:

1) team 1a vs team 1b

and the opposite of that! (team 1b vs team1a)


Tables:

Matches

ID (PK)
TeamID (FK)
DateOfMatch

Teams

ID (PK)
TeamName


I would like to show as well as the results, the names of the teams (teamname)

for this result:

team 1a vs team 1b

I can only get that result, but not:

team1b vs team1a

which is what I would like AS WELL.

Thank-you

 

[SQL_Hell]

Can you post your query?

 

[Tech]

I havent made one up as I am stuck! I am still learning SQL

I guess part of the query would be:

SELECT * FROM Matches WHERE TeamID = 'team1a'

SELECT TeamName FROM Teams WHERE TeamName LIKE 'team%'



Thought I had it in my mind last night, but that's not it!

so I want to check for matches ON a specific team but also want to see if OTHER teams are playing WITH this specific team.

Currently it will work like if the team selected is team1a, it has a match with team1b and will show it

if we select team1b, it should show a match with team1a but it does not as the TeamID (FK) is set to team1a, which is good, but would like to make it more customizable/advanced so any team chosen will show ALL matches involving them, not just them playing other teams

hope it makes sense!

 

[Tech]

doublepost

 

[SQL_Hell]

whats all this for?

One minute you are doing customers/transactions the next you are doing matches/teams?

 

[Tech]

well i do apologize. sorry if it is of any inconvenience - i am doing several projects of my own... i am facing a problem and asking a question on how to go about resolving the problem.

 

[Lightwave]

Hi there Tech,

I've tried to do this kind of thing before and discovered that it's pretty complicated. Essentially you're using combatative mathematics ( a final year maths course ) modelling. I searched about on this forum and found someone that had posted an access database that pretty much did what I was looking for. Funnily enough I got it from someone who had posted it from Linlithgow. I think the quickest thing would be for me to post the zipped file and for you to have a look at it.

The file that I post can create a set of games where everyone plays everyone else from a given date. It's not perfect - I tried to customize it so that it would historically record league results but code is fundamentally wrong and it gets confused between leagues that have the same teams and or players. Mine was originally designed for squash but could work equally well for team games.

Have a look at it and tell me what you think. Note this is an access 03 database but if you have a look behind at the code might help you out...

Mark

 

[SQL_Hell]

ok no probs, just thought you may be a college student or something, and I wont help them because getting me to do it wont help them learn

You will have to join the two tables, like in your other post. If that still doesn't solve your problem then I think you need to change your database design.

 

[Tech]

I had resolved it doing something a bit cheeky, however I will take a look at that zip file
I would have replied to the last comment made by SQL Hell but i wont, being the better person

 

[SQL_Hell]

I had resolved it doing something a bit cheeky, however I will take a look at that zip file
I would have replied to the last comment made by SQL Hell but i wont, being the better person

clearly I have caused you some offense, this was not my intention at all. I only asked what is what all for.... err sorry