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
>
>
