Query - multiple joins will not return records.

[MAtkins]

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

The relatives 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:
<pre>
  Southwest Division | Tomcats  | 17
  Southwest Division | Bears    | 12
  Northeast Division | Tomcats  | 20
  Northeast Division | Lions    | 8
  Northeast Division | Cheetahs | 13
  Southeast Division | Cheetahs | 19
  Southeast Division | Lions    | 12
  Southeast Division | Zebras   | 6
</pre>
Any given team can be in any division.

My Sql is:
<code>
SELECT DISTINCT divisions.Division, teams.Team, AVG(scores.Score) AS Score
FROM (answers
        INNER JOIN ((relatives INNER JOIN divisions ON relatives.FieldID = 1 AND relatives.ValID = divisions.ID)
        INNER JOIN teams ON relatives.FieldID = 2 AND relatives.ValID = teams.ID) ON answers.RelationID = relatives.RelationID)
GROUP BY Division, Team
ORDER BY Division, Team
</code>
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.
 

[Psycho]

Can you export those tables with sample data and attach here? It makes it so much easier to create queries when we can "see" the data and test them.

 

Also, one trick is to start with one table and verify you get the data you expect. Then add one JOIN at a time and verify the results.

Edited February 13, 2015 by Psycho

[Psycho]

OK, looking at your query and your description, I am confused.

 

1. I don't see why you are using Distinct. You are using a GROUP BY which would make it impossible for any of the results not to be distinct

2. You put all the table definitions within parenthesis. Unless you were doing a sub-query, which you are not, I've never seen that format.

3. You say the relatives table is used to link divisions and teams using the "RelationID", but looking at the logic you are using in the query, the way you are using it is overly complicated. The relation table should probably be constructed like this:

 

ID, DivisionID, TeamID

 

Then have a single record for each Division to Team association. That makes this much, much simpler

 

Then, your query might look something like this:

 

SELECT divisions.Division, teams.Team, AVG(scores.Score) AS Score
 
FROM divisions
INNER JOIN relatives ON divisions.ID = relatives.DivisionID
INNER JOIN teams     ON teams.ID = relatives.TeamID
INNER JOIN scores    ON scores.RelationID = relatives.ID
 
GROUP BY Division, Team
ORDER BY Division, Team

[Barand]

I would have thought the relation table redundant in this scenario. It look like a hierarchy

+-------------+
| division    |
+-------------+            +------------+
| div_id      |-----+      |  team      |
| div_name    |     |      +------------+            +------------+
+-------------+     |      |  team_id   |-----+      |  score     |
                    |      |  team_name |     |      +------------+
                    +-----<|  div_id    |     |      | score_id   |
                           +------------+     +-----<| team_id    |
                                                     | date       |
                                                     | score      |
                                                     +------------+

[MAtkins]

I don't think I described it well enough.

 

A person is asked a series of questions about a 'work' and answers them with a score.

Truthfully, there are more than 2 tables. I used 2 just to simplify the problem here.

(divisions, teams, projects, companies, etc.)

 

My client, who sets up the questions can select as many of each entity as they want to represent the work.

For example they may deem 2 divisions and 3 teams as 'attributed' to the work.

 

Putting an FK into one of the 'entity' tables or the scores table won't help.

There is no intrinsic connection between any of the entity tables.

 

I have to produce reports a maximum of 3 tiers deep that show per tier what the average score was.

For example:

SW Division                                       9

     Demolition Team                           7

           Ajax Department Stores          8

 

This report would indicate that for all scores that include the SW Division the score average is 9
For all Demolition Team scores within the SW Division the score average is 7

For all the Ajax Department Stores, within Demolition Team within SW Divsion the score average is 8.

 

I have to be able to show it with any entity in any position.

For example:

Ajax Department Stores

     Demolition Team

          SW Division

 

 

I have a sql dump but I'm not happy with the tables & records.
I'll post it here when I've got something I think that will represent what I need.

Thanks for the reply:)

[Psycho]

I guess I am more confused. You state "There is no intrinsic connection between any of the entity tables." yet, you provide a hierarchy such as

 

 

SW Division                                       9

     Demolition Team                           7

           Ajax Department Stores          8

and talk about the Demolition team within the SW Division. So, am I correct in thinking that each "person" that responds to the survey belongs to a Division, a Team and a Store? If so, then each response can be associated with each of those three entities.

 

I have a feeling that is not the case, though. And, I'm sure I could help if I wanted to invest the time and energy, but this looks like I'd have to invest a good deal of time to even understand the problem.

[Barand]

It sounds like the attached model would serve and would allow "work" definitions such as

+-----------+-----------+--------------+--------+-----------+
| work_name | Division  | Company      | Team   | Project   |
+-----------+-----------+--------------+--------+-----------+
| Work 1    | Southeast |              |        |           |
| Work 1    | Northwest |              |        |           |
| Work 1    |           |              | Team 3 |           |
| Work 1    |           |              | Team 2 |           |
| Work 1    |           |              | Team 1 |           |
+-----------+-----------+--------------+--------+-----------+
| Work 2    | Northeast |              |        |           |
| Work 2    |           | Danish Bacon |        |           |
| Work 2    |           | Body Shop    |        |           |
| Work 2    |           |              | Team 3 |           |
| Work 2    |           |              | Team 2 |           |
+-----------+-----------+--------------+--------+-----------+
| Work 3    |           | Ajax Stores  |        |           |
| Work 3    |           |              |        | Project C |
| Work 3    |           |              |        | Project A |
+-----------+-----------+--------------+--------+-----------+

All I have to do now is figure out how to get your averages from that structure

Edited February 18, 2015 by Barand

[Barand]

I confess I am not sure how your scoring is implemented and how it really fits into the model. Would the score be related to the work_xxxxxx tables?

 

For all Demolition Team scores within the SW Division the score average is 7

For all the Ajax Department Stores, within Demolition Team within SW Divsion the score average is 8.

I agree with Psycho that those statements imply hierarchy

[MAtkins]

Yes but it's really a kind of 'pseudo' hierarchy. I've gotta be able to do it backward also.

The reports mean whatever they do to the client. We've discussed the potential ambiguity of the reports.

Clients want what clients want . . .

 

I'm working on corporate taxes, I'll get back with a new dump when I can.

Thanks for replying:)

[MAtkins]

Barand:

That model looks like pretty much it.

[Barand]

This will calculate the averages

SELECT div_name as division
    , '' as company
    , '' as team
    , '' as project
    , AVG(s.score) as av_score
FROM division d
    LEFT JOIN work_division wd ON d.division_id = wd.division_id
    LEFT JOIN score s ON wd.work_id = s.work_id
GROUP BY d.division_id

UNION

SELECT '' as division
    , c.co_name as company
    , '' as team
    , '' as project
    , AVG(s.score) as av_score
FROM company c
    LEFT JOIN work_company wc ON c.company_id = wc.company_id
    LEFT JOIN score s ON wc.work_id = s.work_id
GROUP BY c.company_id

UNION

SELECT '' as division
    , '' as company
    , t.team_name as team
    , '' as project
    , AVG(s.score) as av_score
FROM team t
    LEFT JOIN work_team wt ON t.team_id = wt.team_id
    LEFT JOIN score s ON wt.work_id = s.work_id
GROUP BY t.team_id

UNION

SELECT '' as division
    , '' as company
    , '' as team
    , p.proj_name as project
    , AVG(s.score) as av_score
FROM project p
    LEFT JOIN work_project wp ON p.project_id = wp.project_id
    LEFT JOIN score s ON wp.work_id = s.work_id
GROUP BY p.project_id;

Results:

+-----------+--------------+--------+-----------+----------+
| division  | company      | team   | project   | av_score |
+-----------+--------------+--------+-----------+----------+
| Northeast |              |        |           |          |
| Northwest |              |        |           |   6.0000 |
| Southeast |              |        |           |   6.6667 |
| Southwest |              |        |           |          |
|           | Ajax Stores  |        |           |   9.0000 |
|           | Body Shop    |        |           |   6.0000 |
|           | Culpepper    |        |           |          |
|           | Danish Bacon |        |           |   5.3333 |
|           |              | Team 1 |           |   6.6667 |
|           |              | Team 2 |           |   6.8000 |
|           |              | Team 3 |           |   6.0000 |
|           |              |        | Project A |   7.6000 |
|           |              |        | Project B |          |
|           |              |        | Project C |   9.0000 |
|           |              |        | Project D |          |
+-----------+--------------+--------+-----------+----------+

[MAtkins]

I've finally solved this (right in the middle of corporate taxes - UGH!).

Rick James on the MySql forum had it.
He just looked at my query and saw what was wrong.

You can't do 2 exclusive INNER JOINS on the same table. They'll just cancel each other out.

You can't do LEFT JOINS on the same table either. You'll just get all records on the left side (ie relations).

 

This gives me exactly what I need. I tested it and compared it to the actual scores & relationships. It's right.

It even handles situations where there was no division and/or no team and/or no organization.
All the averages are correct.

I added the table organizations to be able to make 3 tiers.
I've split the relations table up into multiple tables.
rel_divisions_rl(RelationID, DivisionID)
rel_teams_rl(RelationID, TeamID)
rel_organizations_rl(RelationID, OrgID)

If anybody knows how to make this faster without altering the results I sure would appreciate learning about it.

SELECT divisions.RecordID AS DivisionID, teams.RecordID AS TeamID, (organizations.RecordID) AS OrgID,  MAX(divisions.Division), MAX(teams.Team), MAX(organizations.OrgName), AVG(scores.Score) AS Score
FROM scores INNER JOIN (((relations
    LEFT JOIN (rel_divisions_rl LEFT JOIN divisions ON rel_divisions_rl.DivisionID = divisions.RecordID) ON rel_divisions_rl.RelationID = relations.RecordID)
    LEFT JOIN (rel_teams_rl LEFT JOIN teams ON rel_teams_rl.TeamID = teams.RecordID) ON rel_teams_rl.RelationID = relations.RecordID)
    LEFT JOIN (rel_organizations_rl LEFT JOIN organizations ON rel_organizations_rl.OrgID = organizations.RecordID) ON rel_organizations_rl.RelationID = relations.RecordID)
    ON scores.RelationID = relations.RecordID
GROUP BY DivisionID, TeamID, OrgID
ORDER BY DivisionID, TeamID, OrgID