List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:December 29 2005 3:29am
Subject:Re: issue with subquery
View as plain text  
Dan,

 >SELECT count(*)
 >FROM feeds
 >WHERE feedID IN (
 >  SELECT feeds FROM month_totals
 >  WHERE customerID=9 AND month_unique=1105
 >)
 >expected result should be 4 , i get 1. If i manually put in
 >  SELECT count(*) FROM feeds WHERE feedID IN (1,2,3,4)
 >for instance i get 4

Why the subquery? Aren't you just asking for the count of feeds rows 
joined to month_totals on customerID=9 and month_unique=1105, ie

  SELECT COUNT(*)
  FROM feeds AS f
  INNER JOIN month_totals AS m ON (f.feed_id=m.feeds)
  WHERE m.customerID=9 AND m.month_unique=1105;

If the returned count is not as expected, temporarily replace COUNT(*) 
with DISTINCT feed_id to see what your query is picking up.

PB

-----

Dan Rossi wrote:

> Hi, im having some issues with a sub query in mysql5 , i have a field 
> which is storing a static comma seperate list of primary keys ie 
> 1,2,3,4 , i am then trying to use that to find entries in another 
> table of the same primary keys here is the sql
>
> SELECT count(*) FROM feeds WHERE feedID IN (SELECT feeds FROM 
> month_totals WHERE customerID=9 AND month_unique=1105)
>
> expected result should be 4 , i get 1. If i manually put in
>
> SELECT count(*) FROM feeds WHERE feedID IN (1,2,3,4)
>
> for instance i get 4
>
> querying
>
> SELECT feeds FROM month_totals WHERE customerID=9 AND month_unique=1105
>
> gives me 1,2,3,4 , what seems to be the problem ?
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.8/215 - Release Date: 12/27/2005

Thread
issue with subqueryDan Rossi29 Dec
  • Re: issue with subqueryPeter Brawley29 Dec
  • Re: issue with subqueryJoerg Bruehe29 Dec