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