List:General Discussion« Previous MessageNext Message »
From:Van Date:February 28 2005 10:51pm
Subject:Re: ODD COUNT(*) Results on Self-Join (Bug?)
View as plain text  
Tom:

The second

file_details.FileName NOT LIKE '%_ds.php3' 

Was the culprit.  Not a horrible explanation at all.

Thanks for the extra pair of eyes!

Regards,
Van

=================================================================
http://www.dedserius.com/            -            Linux rocks!!!
=================================================================



Tom Crimmins wrote:

>On Monday, February 28, 2005 14:54, Van wrote:
>
>  
>
>>Tom:
>>
>>I see your point, but the group by is necessary so I can walk through
>>all Song Title groups and get the total number of unique versions of
>>that song.  If I do this:
>>SELECT DISTINCT file_details.Title,
>>         file_details_1.CD,
>>         file_details_1.mp3Name,
>>        COUNT(*) AS cnt
>> FROM file_details LEFT JOIN file_details AS file_details_1
>>         ON file_details.Title = file_details_1.Title
>> WHERE (((file_details.Type) Like 'Song%')
>>         AND file_details.CD = 'Wasted Tears'
>>         AND file_details_1.Type LIKE 'Song%'
>>         AND file_details_1.FileName NOT LIKE '%_ds.php3'
>>         AND file_details.Title = 'Seems I\'ll')
>>GROUP BY file_details.Title
>> ORDER BY file_details_1.Title;
>>
>>I get this:
>>+------------+--------------+-----------------------+-----+
>>    
>>
>>>Title      | CD           | mp3Name               | cnt |
>>>      
>>>
>>+------------+--------------+-----------------------+-----+
>>    
>>
>>>Seems I'll | Wasted Tears | mp3/mp3s/seemsill.mp3 |   6 |
>>>      
>>>
>>+------------+--------------+-----------------------+-----+
>>
>>COUNT = 6; And, it's distinct.  Watch what happens when I do a row
>>listing without the grouping;
>>
>>SELECT DISTINCT file_details.Title,
>>         file_details_1.CD,
>>         file_details_1.mp3Name
>> FROM file_details LEFT JOIN file_details AS file_details_1
>>         ON file_details.Title = file_details_1.Title
>> WHERE (((file_details.Type) Like 'Song%')
>>         AND file_details.CD = 'Wasted Tears'
>>         AND file_details_1.Type LIKE 'Song%'
>>         AND file_details_1.FileName NOT LIKE '%_ds.php3'
>>         AND file_details.Title = 'Seems I\'ll')
>> ORDER BY file_details_1.Title;
>>+------------+-----------------+-------------------------------------+
>>    
>>
>>>Title      | CD              | mp3Name                             |
>>>      
>>>
>>+------------+-----------------+-------------------------------------+
>>    
>>
>>>Seems I'll | Fear of Success | mp3/mp3s/SeemsIll20031029.mp3       |
>>>Seems I'll | n/a             | mp3/mp3s/SeemsIllUnpluggedMixed.mp3 |
>>>Seems I'll | Wasted Tears    | mp3/mp3s/seemsill.mp3               |
>>>      
>>>
>>+------------+-----------------+-------------------------------------+
>>
>>So, here the listing is the group of records I want and there are 3,
>>and it's distinct.
>>
>>Why when I put the group on this query (which is what I need) does it
>>double the count?
>>
>>I think it's a bug.
>>
>>    
>>
>
>If you displayed all the fields from both tables, I think you would 
>understand the join better. This is somewhat difficult to explain, but 
>because you are only saying file_details_1.FileName NOT LIKE '%_ds.php3' 
>and not file_details.FileName NOT LIKE '%_ds.php3' as well, this will in 
>effect double all of your groupings since you are not grouping by filename, 
>and two rows from table 1 will be left after the where clause to be joined 
>to the three rows left in table 2. This in effect gives you your six rows,
>or
>3 groups of 2 with you group by clause.
>
>Like I said above, I would suggest showing all of the fields 
>ie. SELECT file_details.*, file_details_1.* FROM ....., so you can get a 
>better idea of what is going on here. Also, there really isn't any reason 
>to do a left join here. An inner join would work just fine since you are 
>joining a table with itself on the same field there will always be a match. 
>
>By the way this is a horrible explaination, maybe someone else can do a
>better 
>job of it.
>  
>
>Regards,
>
>  
>
Thread
Mysql tuning - server Crash 1Deluxe Web26 Feb
Re: Mysql tuning - server Crash 1Heikki Tuuri27 Feb
  • Re: Mysql tuning - server Crash 1Deluxe Web27 Feb
Re: Mysql tuning - server Crash 1Heikki Tuuri27 Feb
  • RE: Mysql tuning - server Crash 1Donny Simonton28 Feb
    • ODD COUNT(*) Results on Self-Join (Bug?)Van28 Feb
    • Re: Mysql tuning - server Crash 1Deluxe Web28 Feb
    • Re: Mysql tuning - server Crash 1Deluxe Web28 Feb
RE: ODD COUNT(*) Results on Self-Join (Bug?)Tom Crimmins28 Feb
  • Re: ODD COUNT(*) Results on Self-Join (Bug?)Van28 Feb
Re: Mysql tuning - server Crash 1Heikki Tuuri28 Feb
Re: Mysql tuning - server Crash 1Heikki Tuuri28 Feb
  • Re: Mysql tuning - server Crash 1Deluxe Web28 Feb
Re: Mysql tuning - server Crash 1Heikki Tuuri28 Feb
  • Re: Mysql tuning - server Crash 1Deluxe Web28 Feb
RE: ODD COUNT(*) Results on Self-Join (Bug?)Tom Crimmins28 Feb
  • Re: ODD COUNT(*) Results on Self-Join (Bug?)Van28 Feb