weird SQL required 1

[theotrain]

im trying to do a query thats driving me to drink.

i think i could get it to work if i could do one fairly simple thing. heres a boiled down example:

[tt]
idName idNum
-----------------
fred 6
fred 4
fred 17
sam 11
joe 17
[/tt]

what if i wanted to select all rows containing all idNames where the idNum = 17 in any case. In this example I would want all three rows containing the idName 'Fred' returned because there is a Fred with the idNum 17... as well as the one row containing the idName "joe".

as it is i can only think of how to do it using mutiple queries. one query to find the idNames, and additional queries to find the other rows containing those idNames. but i need to do a cfchart on the results, so the multiple query solution, in addition to being wasteful, doesnt work for me.

thanks for taking the time!

 

[Golom]

Kinda like this?

Select idName, idNum

From SomeTable

Where idName IN (Select idName From SomeTable
                 Where idNum = 17)

 

[theotrain]

nice. ahh the elegance of SQL, especially when you know how to use it.

thats got me pretty close but im still having a problem. i need to use both GROUP BY and ORDER BY in my query. at least i think i do. which is OK, except i keep getting errors. another example:

[tt]
SELECT AVG(x.scoreCategory2) AS Retention, AVG(x.scoreCategory3) AS Vocabulary
FROM quizzes x, students t, episodes e
WHERE x.quizID = e.episodeName AND e.level = t.currentLevel AND t.ID = 1 AND x.quizID IN (SELECT quizID FROM quizzes WHERE episodeSection = 5)
GROUP BY x.quizID
ORDER BY x.quizDate
[/tt]

this is a slightly simplified version of the query i want. but it blows up when i add the ORDER BY statement (i thought) because there is no x.quizDate in the select statement. but i cant put one there either, or i get this error:

"You tried to execute a query that does not include the specified expression 'quizDate' as part of an aggregate function."


i can understand why it would choke on this. the AVG() functions are doing math on several rows, so if you try to order the rows, which row should it use, since its really a group of rows you are dealing with?

it would be fine to use any row of the group for the order by date criteria... but i dont know how to make it work.

appreciate the help! so close i can taste it.

 

[Golom]

SELECT MIN(x.quizDate) As QDate,
       AVG(x.scoreCategory2) AS Retention, 
       AVG(x.scoreCategory3) AS Vocabulary

FROM (quizzes x 
      INNER JOIN episodes e ON x.quizID = e.episodeName) 
      INNER JOIN students t ON e.level  = t.currentLevel 

WHERE t.ID = 1 
  AND x.quizID IN (SELECT quizID FROM quizzes 
                   WHERE episodeSection = 5)

GROUP BY x.quizID

ORDER BY 1

 

[Golom]

Or ... if you don't want the date in your result

SELECT AVG(x.scoreCategory2) AS Retention, 
       AVG(x.scoreCategory3) AS Vocabulary

FROM (quizzes x 
      INNER JOIN episodes e ON x.quizID = e.episodeName) 
      INNER JOIN students t ON e.level  = t.currentLevel 

WHERE t.ID = 1 
  AND x.quizID IN (SELECT quizID FROM quizzes 
                   WHERE episodeSection = 5)

GROUP BY x.quizID

ORDER BY MIN(x.quizDate)

 

[theotrain]

brilliant. thanks Golom, those are both perfect

just what i needed to get the order working. your FROM statement with the innerjoins is something i have to study carefully as i have never used that before. (believe it or not). i greatly appreciate the lesson.

 

[lespaul]

be sure to check out Understanding SQL Joins

Leslie

Have you met Hardy Heron?