Jump to content


Photo

Mysql Query - Multiple Joins - Won't Return Records


  • Please log in to reply
1 reply to this topic

#1 MAtkins

MAtkins

    New Member

  • Members
  • Pip
  • 2 posts

Posted 13 February 2015 - 06:45 PM

I've got 4 tables:
divisions (ID, Division)
teams (ID, Team)
scores(ID, RelationID, Score)
relations (ID, RelationID, FieldID, ValID)

The relations table is to 'link' divisions and teams by RelationID
The FieldID defines the table (1=divisions; 2=teams)
The ValID = the ID of the given table.
So, my fk is a combination of FieldID & ValID

The output should look something like this:

Southwest Division | Pneumatics | 17
Southwest Division | Refrigeration | 12
Northeast Division | Pneumatics | 20
Northeast Division | Underground | 8
Northeast Division | Networking | 13
Southeast Division | Networking | 19
Southeast Division | Underground | 12
Southeast Division | Efg | 6

Any given team can be in any number of divisions.

My Sql is:

SELECT DISTINCT divisions.Division, teams.Team, AVG(scores.Score) AS Score
FROM (scores
INNER JOIN ((relations INNER JOIN divisions ON relations.FieldID = 1 AND relations.ValID = divisions.ID)
INNER JOIN teams ON relations.FieldID = 2 AND relations.ValID = teams.ID)
ON scores.RelationID = relations.RelationID)
GROUP BY Division, Team
ORDER BY Division, Team

This sql returns no records. Even if I remove the answers, GROUP and ORDER BY I get no records.

How can I make the tables return what I need? I'd redesign the whole thing if needed.

Attached Files



#2 MAtkins

MAtkins

    New Member

  • Members
  • Pip
  • 2 posts

Posted 14 February 2015 - 09:43 AM

I needed to mention that a given team can be affiliated with multiple divisions and that multiple divisions and/or teams can be affiliated with 1 score.
The attachment is a dump file so you can recreate the tables in MySql easily.

Edited by MAtkins, 14 February 2015 - 09:47 AM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users