ACCESS query

[gnom]

I have a following problem - I can't create a proper SQL query for my a database
http://www50.brinkster.com/seekasp/sql/view.gif

I want to get fields of Game table + information about team against and team with. I tried such query:
SELECT * FROM GAME INNER JOIN TEAM ON Game.team_with_id=Team.id or team_against_id=Team.id ORDER BY Game.id

But it doubles rows:
http://www50.brinkster.com/seekasp/sql/table.gif

What should be the proper query to get information about t.against and t.with in a one row.

If you need to see content of all table please visit:
Data (http://www50.brinkster.com/seekasp/sql/tables.asp)

This is a project for my studies, all data are taken from the blue... ;)

[ScaryBinary]

Hmmm...try is to use two INNER JOINS instead of one INNER JOIN with an "OR".

Instead of what you had:SELECT * FROM GAME INNER JOIN TEAM ON Game.team_with_id=Team.id or team_against_id=Team.id ORDER BY Game.idtry something likeSELECT * FROM GAME
INNER JOIN Team ON Game.team_with_id = Team.id
INNER JOIN Team AS Team1 ON Game.team_against_id = Team1.id

A few things to note: I'm not sure, but I think since you're joining in the "Team" table twice, you'll have to rename one of them using the "AS" keyword. Also, you should probably replace the asterisk "*" in the SELECT clause with just the fields you want to see.

The reason you get duplicate records is because of that "or" in your JOIN clause. The query takes a single record from the Game table and finds the records in Team table that have the same id's as the "with" and "against" id's. Since you're looking for two matching team ids for each single game, Access displays the data for the first match in one row and the data for the second match in the next row, duplicating the data from the Game table. I hope that makes sense, I can't think of a better way to describe it right now. :p

The example I gave should put data from both the "with" team and "against" team in the same row.

I'll post this reply, then try a little sample in my Access 2000.

Post first and ask questions later, that's what I always say!

[gnom]

I'm afraid your query has a syntax error. When you want to join more than one table, you have to use brackets; for example:
SELECT * FROM Club INNER JOIN (Sponsor INNER JOIN Country ON Country.id=Sponsor.country_id) ON Club.id=Sponsor.club_id ORDER BY Club.name
This syntax is specified the in Access help and it do works.
But the query built like you wrote, with (and without) proper syntax applied:
SELECT * FROM GAME INNER JOIN ( Game INNER JOIN Team AS Team1 ON Game.team_against_id = Team1.id) ON Game.team_with_id = Team.id
doesn't work.

[gnom]

I've found the solution, but unfortunately without join:
SELECT * FROM Team AS Team,Game, Team AS Team1 where Game.team_with_id = Team.id and Game.team_against_id = Team1.id
So how should the query with join look like?

[ScaryBinary]

In my Test database I used this query:SELECT Game.Date, Team.Name AS TeamWithName, Team_1.Name AS TeamAgainstName
FROM (Game INNER JOIN Team ON Game.team_with_id=Team.id)
INNER JOIN Team AS Team_1 ON Game.team_against_id=Team_1.id;
and it behaved how I expected it to, at least. I got this result (with my fake data):Date TeamWithName TeamAgainstName
-------- ------------ ---------------
1/1/2005 Ogary Konikol
1/3/2005 Tigers Konikol
4/5/2005 Kickers Ogary

Are you looking for something like that?

I forgot about how Access requires paretheses for joins. Typical dopey Microsoft approach (this isn't standard SQL).

You misquoted my query in your other post. If you start with my original post and put the parentheses in, you get the same results as above (with a few extra fields).

Let me know if these aren't the query results you're looking for...?

[ScaryBinary]

PS: Just ran your query (using the WHERE clause instead of the JOIN ONs) and got the same results as my query.

Thats a good sign. :p

[gnom]

Yes, your query works!! Thanks a lot!!
You are my gooroo of computer wisdom.

[ScaryBinary]

Originally posted by gnom
You are my gooroo of computer wisdom.

Aw, shucks! :D