Re: 2 Query with same criteria giving different number of rows

[Chris Tucker]

You're getting a distinct on just the prod_num in the first query, but 
are requesting distinct prod_num, description, line, and content in the 
second one: if any of those last three columns are different between 
rows, you will see ones additional to the count you get in the first 
query.  You either need to count the same set of distinct columns in the 
first query or GROUP BY prod_num in the second.

http://www.mysql.com/doc/en/SELECT.html
http://www.mysql.com/doc/en/GROUP-BY-Functions.html
Chris

Hector Del Curto wrote:
I have this 2 queries:

SELECT COUNT(DISTINCT(LEFT(p.prod_num,5))) FROM products p, tlines l,
prod_line pl WHERE l.Line = 'Solaris' AND l.id=pl.line_id AND pl.prod_id =
p.id_product
SELECT DISTINCT(LEFT(p.prod_num,5))as prod_num, p.description AS
description,l.Line as line,l.Content as content FROM products p, tlines l,
prod_line pl WHERE l.Line = 'Solaris' AND l.id=pl.line_id AND pl.prod_id =
p.id_product
The only difference between them is that in one I want the count of rows and
on the other I want the columns.
When I run the queries I get for example 24 as result of the count and on
the other query I get 26 records.
In those 26 records I get 2 prod_num duplicated when I'm asking for
DISTINCT.
Now, the other thing is if I only leave the prod_num column the number of
rows are the same, if I add another column it changes to 26.
I know I have some duplicates on the second table, and that's affecting the
results, but I don't know why.
Any hint on why is that happening?


                                                                                       
                 Thanks,
                                                                                       
                         Hector



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]