List:General Discussion« Previous MessageNext Message »
From:Illyes Laszlo Date:October 23 2003 4:41am
Subject:Re: 2 Query with same criteria giving different number of rows
View as plain text  
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?
unsub=illyeslaszlo@stripped


Laszlo Illyes
Teaching-assistant
Sapientia University
(Csikszereda) Miercurea-Ciuc
Tel:+40266317310
Fax:+40266317310/+40266371121
Mobil:+40740055706
E-mail: illyeslaszlo@stripped

Thread
MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri22 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Chris Nolan22 Oct
  • 2 Query with same criteria giving different number of rowsHector Del Curto22 Oct
    • Re: 2 Query with same criteria giving different number of rowsChris Tucker22 Oct
    • Re: 2 Query with same criteria giving different number of rowsIllyes Laszlo23 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Eduardo D Piovesam23 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Eduardo D Piovesam23 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri24 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Martijn Tonies24 Oct
    • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Chris Nolan24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Eduardo D Piovesam24 Oct
    • MySQL/InnoDB-4.0.16 +Optimizer behaviourSergey S. Kostyliov29 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Eduardo D Piovesam24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Martijn Tonies24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri27 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri26 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Martijn Tonies26 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Jon Hancock27 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Chris Nolan27 Oct
    • InnoDB on Raw partitions in OSX (was Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1)Gabriel Ricard27 Oct
  • Partial replicate InnoDB -> MyISAMJon Hancock28 Oct
    • Re: Partial replicate InnoDB -> MyISAMChris Nolan28 Oct
  • Re: Partial replicate InnoDB -> MyISAMJon Hancock28 Oct
Re: InnoDB on Raw partitions in OSX (was Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1)Heikki Tuuri27 Oct