Hello. We use MySQL 3.23.38 with our website http://www.rlsnet.ru. This new MySQL version is much better than the previous one (sorry, can't get the exact version number), but I noticed one strange thing which I wanna write about. When I make
select TN0MNN.torgn_id, Rlsprepr.prepr_id, Rlsbopis.opis_id, count(distinct Rlsbopis.opis_id) as c from TN0MNN, Rlsprepr left join Rlsopipr using(prepr_id) left join Rlsbopis using(opis_id) where TN0MNN.torgn_id=Rlsprepr.torgn_id group by torgn_id having c=0 I get one result, and when I make select TN0MNN.torgn_id, Rlsprepr.prepr_id, Rlsbopis.opis_id from TN0MNN, Rlsprepr left join Rlsopipr using(prepr_id) left join Rlsbopis using(opis_id) where TN0MNN.torgn_id=Rlsprepr.torgn_id group by torgn_id having count(distinct Rlsbopis.opis_id) =0 I get a different one. The only difference between them is that in the first I get "count(distinct Rlsbopis.opis_id) as c" as a result field and then I have "having c=0" in the end, while in the second query I do the same thing by "having count(distinct Rlsbopis.opis_id) =0", that's I don't have the count as a result field. I thought the queries should yield the same results, but that's not so. The queries are supposed to get the names of medicines that have no text description. Every medicine name (so called tradename, torgn_id) is connected with one or more medicines (prepr_id) that have it, each of medicines may have a text description (opis_id). Thus, any tradename, via all its medicines may have zero or more text descriptions. As I found, the difference in the results of the queries is caused by those tradenames that have both medicines with description and those without. That is, if a tradename has some medicines and all of them have a description, both the queries consider that the number of the descriptions is more than zero. If a tradename has some medicines and none of them has a description, both the queries consider that the number of descriptions is zero. But if a tradename has some medicines and some of them have descriptions while others don't, the first query (which is correct!) considers that the number of descriptions is more than zero, while the second (incorrect!) considers it to be zero. Is this a bug or my misunderstanding of the situation? --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
