Query - multiple joins won't return any records.

[MAtkins59]

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:

[pre]
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
[/pre]
Any given team can be in any combination of divisions. Also, it has to be interchangable (ie divisions per team)

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.
The attachment is a sql dump of the tables & records.

 

[jpadie]

can you describe your structure in business terms and entity models? i am unable to derive your need from the explanation you have given.

sfaics you have

a bunch of teams, each of which have an ID and a name.
each team belongs to one or more divisions. A division has an ID and a name
the teams do things. and as such they get points.

you want to derive the average points per division per team.

if so i would have a join table that just had the divisionID and teamID in it. I don't see the need for the complexity of a FieldID nor a relationID. Which means I am probably misunderstanding your business needs. Nor do i understand what the valID in the relations table might be.

then a simple join to the division-team table will allow you to retrieve a list of divisions, teams and scores.

if the scores are also subject to differentiation on division then you will need either a join with an AND or to use a subselect or to make your scores table more detailed, having a team and division id within.

i do not understand your comment that divisions and teams must be interchangeable. perhaps you could describe that in business terms as it is difficult to see how an object that holds a collection of other objects can hold itself or be held by objects that it holds. You end up with a moebius ring.

 

[jpadie]

is the SAME team in each division? so the SAME pneumatics team in NE and SE? or simply two different teams with the same name?

if it is the SAME team then your scores table will need to contain the divisionID and the teamID to differentiate.

if it is a different team then just the teamID will suffice and the teams table should contain a column for divisionID.

Just a suggestion: use descriptive field names for the PK in your tables. recordID is perhaps not as helpful as, say, divisionID or teamID, particularly in the FK usage.

 

[MAtkins59]

I've got a non-disclosure so I really can't divulge the app. The problem is straight forward though.
Yes, the same team can be in any division and visa versa so an fk in either of those tables is no help.
I neglected to point out that more than 1 division and/or more than 1 team can be affiliated with 1 score.
That said, using an Fk in the scores table won't work and that's my problem.

 

[jpadie]

You still need to describe the relationships better. Putting together all your statements it is still not clear to me what you are trying to achieve.

Perhaps start with a description of a team and a division and define the relationship between them. In normal business terms not SQL relationships.

If you genuinely cannot even describe relationships because of your NDA then you are unlikely to be able to get much help from a board such as this.

 

[MAtkins59]

OK, I'll try.
The teams table amounts to a table of values. The divisions table amounts to a table of values.
There is no implicit connection between teams and divisions.
A person enters a score (based on a question) and attributes the score to a combination of divisions and/or teams.

Multiple divisions may be 'attributed to' a given score. Multiple teams may also be 'attributed to' a given score.
So a given score could be 'attributed to' 2 divisions and 3 teams (ie a 'relationship').
Further, more than 1 score may be attributed to the same 2 divisions and 3 teams (ie the same relationship).

I need to be able to produce reports that show for each division, for each team what the average score was.
I need to be able to show it as teams per division OR divisions per team.

 

[jpadie]

So what is the difference between a team and a division?

How can a division have a score?

 

[MAtkins59]

I'm starting to think you're just playing with me.
A database programmer should be able to pretty clearly see what my problem is.
I even left you a dump file.

 

[jpadie]

look at my profile if you doubt my credentials.

up to you to consider whether my questions are reasonable. If you want my help then I need to understand the problem. I don't on the information that you have provided.

the dump file exemplifies only that you have a problem. the data would be useful if the schema and relationships were explained in a meaningful manner.

If you cannot describe the relationships using words, use an entity modelling program or piece of paper and draw out the relationships between the tables using standard x<->x notation.

the difficulty you present is here

There is no implicit connection between teams and divisions.

versus

I need to be able to produce reports that show for each division, for each team what the average score was.
I need to be able to show it as teams per division OR divisions per team.

in the first you are stating there is no relationship. in the second quotation you are inferring that there must be a relationship.

but you have not articulated what the relationship might be nor how the relationship may be reversed (containers can very rarely be contained by that which they are intended to contain).

But of course if you think that this is such an easy problem and that anyone can see it, perhaps you're right! I don't claim to have the answer to everything and I've certainly written my share of sub-optimal queries in my time.

 

[MAtkins59]

Sorry, this is frustrating and I can't edit replies here.
The teams table & the divisions table are filled with values entered by the client.
"A person enters a score (based on a question) and attributes the score to a combination of divisions and/or teams."

 

[jpadie]

so you are stating that there is no relationship between a team and a division.

fine.

then I see no way that you can produce a report of teams per division. Which would be why you get a nil record result.

 

[MAtkins59]

There is no implicit relationship between the 2 tables.
A piece of work is done and specific 'entities' (for lack of a better term) are involved.
The only relationship between the 'entities' is that they were both involved with the work.
This is based on what the client selects for the given 'work'.
I can't say what they represent. They represent whatever the client thinks they represent.

A person is asked questions on the work and answers with scores.
It appears as though you're telling me that I cannot develop what this client wants.
Myself, I can only see 1 way to do it and it's a nasty design.

If I create a table with id fields for each possible entity (ie DivisionID, TeamID) and use varchars instead of ints I can comma separate all the ids of a given kind within a given field in a given record.
That way I can record more than one division, more than one team, more than one whatever, per score.
That's a bloated, nasty design and I'd *really* rather not do that.
I could produce the reports they want but it'd be awful slow and the database would be bloated.

I was hoping another professional database programmer might have a better solution.

 

[ChrisHirst]

I was hoping another professional database programmer might have a better solution.

Redesign the database structure and schema.

instead of ints I can comma separate all the ids of a given kind within a given field in a given record.

Oh I really DO hope that is not a serious suggestion!

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[MAtkins59]

Chris: "Redesign the database structure and schema." Excellent idea! HOW?
If I knew that I never would have started this thread.

Chris: "Oh I really DO hope that is not a serious suggestion!" Did you read the whole reply?
I said I can do it that way, as if to say I don't think you're grasping the problem, but the design really stinks.

 

[jpadie]

here is a query that will give you the answer per division OR per team. without you being crystal clear about the relationship between teams and divisions i really don't see how you can be helped more.

select d.division, avg(s.score)
from divisions d
join relations r
on r.FieldID = 1 AND r.valID = d.recordID
join scores s
on r.relationID = s.relationID
group by d.division
order by d.recordID

[b]division   [tab]avg(s.score)[/b]
Div One    [tab]6.1250
Div Two    [tab]5.7500
Div Three  [tab]5.7500
Div Four   [tab]5.7500

 

[jpadie]

my suggestion mirrors that of Chris however. if you're struggling on output then redesign the storage.

we can help with that, with pleasure, but you would have to describe the business data that you are trying to record and the relationships that exist. and unfortunately you say you cannot do that. perhaps you might ask your clients for a derogation from your NDA?

 

[jpadie]

as an aside - what should the output be from the dataset that you have provided?

there are a bunch of queries pivoting around the relationID that produce results but without understanding what you want, the validity of the results is somewhat in question.

perhaps we can derive some meaning from knowing the output from the provided dataset.

 

[MAtkins59]

jpadie thanks for your replies.
I'm hoping you figured out that your sql wouldn't work. I needed teams PER division or divisions PER team.
I got the solution from Rick James on the MySql forum. 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 cancel each other out.
You can't do LEFT JOINS either because 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

 

[jpadie]

thanks for posting back.

well done to Mr Jones for being able to penetrate your imagination via the window of your broken query. My approach is always the other way around - describe the business need and then craft the query.

as you have implied - your choice of table architecture results in a terrible query that will run like a dog. There are those here whom you might trust to help with your architecture but for my part I would need a description of the problem in my terms to be able to grok it enough to come up with a solution.

 

[jpadie]

I'd add that you don't need to split the relations table as per Mr James. it is just 'normal' (in the db sense of the word) to do so.

in my work-in-progress code I was using derived tables to do the same

join (select * from relations where fieldID=1) relT
ON relT.relationID = team.recordID

etc.

but this may still overall be a bad solution. In toto I agree with Peter Brawley (and I see he largely echos the sentiments expressed by me and Chris in this chain).

link to mysql forum thread