List:General Discussion« Previous MessageNext Message »
From:Gerald Taylor Date:July 12 2004 1:42pm
Subject:Re: query gets count wrong
View as plain text  
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;

Thread
Cost of joins?Margaret MacDonald9 Jul
  • Re: Cost of joins?Jigal van Hemert9 Jul
  • Re: Cost of joins?Martijn Tonies9 Jul
    • Re: Cost of joins?Alec.Cawley9 Jul
    • RE: Cost of joins?Lachlan Mulcahy12 Jul
      • Re: Cost of joins?Martijn Tonies12 Jul
      • Re: query gets count wrongGerald Taylor12 Jul
  • Re: Cost of joins?Alec.Cawley9 Jul
  • Re: Cost of joins?Jigal van Hemert9 Jul
    • Re: Cost of joins?Jochem van Dieten9 Jul
  • Re: Cost of joins?Martijn Tonies9 Jul
    • Re: Cost of joins?Alec.Cawley9 Jul
      • Re: Cost of joins?Jochem van Dieten9 Jul
    • Re: Cost of joins?(Michael Johnson)9 Jul
  • Re: Cost of joins?Jigal van Hemert9 Jul
  • Re: Cost of joins?Martijn Tonies9 Jul
RE: Cost of joins?Bob)9 Jul
Re: Cost of joins?Martijn Tonies9 Jul
Re: Cost of joins?Martijn Tonies9 Jul
Re: query gets count wrongSGreen12 Jul