Re: 2 Query with same criteria giving different number of rows
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]
Re: 2 Query with same criteria giving different number of rows
Hi! Think about using DISTINCT and COUNT together. I don't think it is a best coice. But maybe I'm wrong. Please Paul Dubois make your point. regards On Wed, 22 Oct 2003 13:25:20 -0400, 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/mysql? [EMAIL PROTECTED] Laszlo Illyes Teaching-assistant Sapientia University (Csikszereda) Miercurea-Ciuc Tel:+40266317310 Fax:+40266317310/+40266371121 Mobil:+40740055706 E-mail: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
