I am an anal single query-oholic. I know I could do this in 2 queries
I have a query involving several related tables and I have attempted to reduce it down to what causes "not what I want" results.
I am attempting to fill a summary table.
For each main item in this table I want to count the number of child items in a related table that point to it. This is fine.
I have a third table called ratings which rates individual child items
and it is the problem.
The table qxe contains e_id which is a foreign key to the main table
and q_id which is a foreign key to the actual child items. I had to set things up this way because a q might belong to more than one e. Right now I am not even interested in anything about the q's data I just want
to count them.
The ratings table is the problem because any q can have an arbitrary number of ratings. so a rating has q_id and e_id as well as another key that combines to form a multipart key.
instead of the number of q_ids in the qxe table that have e_id equal to the the current e_id, I am getting as e_count the total number of ratings for that e_id which is a huge humber. I know I need another
constraint but I cant figure out what it is. What constraint can I add to make this query do what I want while still being able to average
the ratings.
SELECT e.e_id, e.e_code, COUNT(qxe.q_id) as e_count, avg(ratings.r_quality) as avqual
FROM e, qxe, ratings
WHERE e.e_id = qxe.e_id
AND ratings.e_id = e.e_id
AND ratings.q_id = qxe.q_id
AND ratings.e_id = qxe.e_id
GROUP BY e.e_id;
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
