WHERE, GROUP BY and HAVING difficulties over JOINS

[mrjmk]

Hi,

I'm having some difficulties with the following...

I have a table of persons (pId, name), one of qualifications (qId, name, level, sublevel) and a table of candidates (cId, qId, pId, date).

The candidates table links the persons and qualifications tables. The qualifications have a level and a sublevel because some of them can have more than one part (like a theory and then practical). The levels are backwards (level 4 is lover than 1).

For ease with levels having different numbers of requirements, the sublevels are done backwards towards 1, i.e. when you have level XX, sublevel 1, you have completed level XX.

I'm trying to return the highest qualification level that each person has fully acheived but don't seem to be able to get it.

Here is the output of all of the people who have finished a qualification:

SELECT persons.name, persons.pId, quals.name, quals.level
FROM persons JOIN candidates USING(pId) JOIN quals USING(qId)
WHERE quals.sublevel=1 
ORDER BY persons.name
-->
name   	  	pId   	 name   	 level
Chopin 	 	1 	Grade 3 	3
Chopin 	 	1 	Grade 2 	2
Rachmaninoff 	4 	Grade 3 	3
Rachmaninoff 	4 	Grade 4 	4

Both Chopin and Rachmaninoff have completed two levels. Chopin's highest is 2, Rachmaninoff's 3.

Now, what i want to do is GROUP BY pId but, as you can see, I don't necessarily get the row that I want.

SELECT persons.name, candidates.pId, quals.name, min(quals.level) as highestGrade
FROM persons JOIN candidates USING(pId) JOIN quals USING(qId)
WHERE quals.sublevel=1 
GROUP BY pId
ORDER BY persons.name
-->
name 	   	 pId   	 name   	 highestGrade
Chopin 	 	1 	[COLOR=red]Grade 3 	2[/color]
Rachmaninoff 	4 	[COLOR=red]Grade 4 	3[/color]

As I understand it, HAVING would operate on this after the GROUP BY and before ORDER BY and so wouldn't do this. The min() is not accessible from the WHERE clause. My efforts with subqueries have failed becuase they return more than one row.

... WHERE level=(SELECT min(level) FROM quals JOIN candidates GROUP BY pId) ...

I'd be really grateful if anyone was able to shed some light on whether I'm going about this in the wrong way (are my data structures causing this?) or whether I am overlooking something simple here. Is there a means of supplying data to your subqueries?

Thanks very much,
j

 

[r937]

to avoid getting unpredictable results, always make sure the SELECT matches the GROUP BY:

SELECT [b]persons.name
     , candidates.pId
     , quals.name[/b]
     , min(quals.level) as highestGrade
  FROM persons 
  JOIN candidates USING(pId) 
  JOIN quals USING(qId)
 WHERE quals.sublevel=1 
GROUP 
    BY [b]persons.name
     , candidates.pId
     , quals.name[/b]
ORDER 
    BY persons.name

see the mysql manual section called "GROUP BY with hidden fields" for an explanation


r937.com | rudy.ca

 

[mrjmk]

Thanks. That link wasn't quite what I was looking for but I followed the link to "3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field", where I used one of suggestions in the comments and did a self-join on the joined candidates and quals tables to find the min instance for each person.

If anyone's interested...

SELECT c1.cId, q1.qId, q1.name, q1.level, q1.sublevel, c1.pId, p.name
FROM (
(
candidates c1
INNER JOIN quals q1
USING ( qId )
)
INNER JOIN (
candidates c2
INNER JOIN quals q2
USING ( qId )
) ON q1.level < q2.level
)
INNER JOIN persons p ON p.pId = c1.pId
WHERE q1.sublevel =1
GROUP BY c1.pId

Thanks very much.

 

[guelphdad]

your group by clause is incorrect and should include each item in your select statement. since it doesn't all other databases would fail and give you an error message. mysql allows you to GROUP BY HIDDEN FIELDS but warns you about ending up with incorrect data as a result.

 

[mrjmk]

SELECT c1.rId, q1.qId, q1.name, q1.level, q1.sublevel, c1.pId, p.name
FROM (
(
candidates c1
INNER JOIN quals q1
USING ( qId )
)
INNER JOIN (
candidates c2
INNER JOIN quals q2
USING ( qId )
) ON q1.level < q2.level
)
INNER JOIN persons p ON p.pId = c1.pId
WHERE q1.sublevel =1
GROUP BY c1.pId
-->
rId 	qId 	name 	level 	sublevel 	pId 	name
10 	6 	Grade 2 	2 	1 	1 	Chopin
8 	4 	Grade 3 	3 	1 	4 	Rachmaninoff

I hear you and I've read the manual on that topic. My problem is that at the moment this seems to give me what I want, whereas adding the extra GROUP BY terms gives...

SELECT c1.rId, q1.qId, q1.name, q1.level, q1.sublevel, c1.pId, p.name
FROM (
(
candidates c1
INNER JOIN quals q1
USING ( qId )
)
INNER JOIN (
candidates c2
INNER JOIN quals q2
USING ( qId )
) ON q1.level < q2.level
)
INNER JOIN persons p ON p.pId = c1.pId
WHERE q1.sublevel =1
GROUP BY c1.pId, c1.rId, q1.qId, q1.name, q1.level, q1.sublevel, p.name

-->

rId   	 qId   	 name   	 level   	 sublevel   	 pId   	 name
1 	4 	Grade 3 	3 	1 	1 	Chopin
10 	6 	Grade 2 	2 	1 	1 	Chopin
8 	4 	Grade 3 	3 	1 	4 	Rachmaninoff

This gives two rows for Chopin- i.e. not his highest qualification but all of those completed.

Is there a way around this?

Your help is appreciated.