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.
>
> Tom Crimmins wrote:
>
>> On Sunday, February 27, 2005 19:20, Van wrote:
>>
>> Hi Van,
>>
>>
>>
>>> Greetings:
>>>
>>> I've got a table that has the following fields that are relevant to
>>> my self-join: FileName | varchar(100) | | MUL |
>>> |
>>> Title | varchar(45) | | MUL |
>>> |
>>> Type | varchar(20) | | | HTML
>>> |
>>> mp3Name | varchar(100) | | |
>>> |
>>> CD | varchar(25) | | |
>>> |
>>>
>>> Here are the relevant values for the fields for the song Seems I'll:
>>> +------------------------------------+------------+--------+----------
>>> FileName | Title | Type |
>>> mp3Name | CD |
>>> +------------------------------------+------------+--------+----------
>>> mp3/php3/seemsill.php3 | Seems I'll | Song |
>>> mp3/mp3s/seemsill.mp3 | Wasted Tears |
>>> mp3/php3/SeemsIllUnplugged.php3 | Seems I'll | Song |
>>> mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | n/a |
>>> mp3/php3/SeemsIll20031029.php3 | Seems I'll | Song |
>>> mp3/mp3s/SeemsIll20031029.mp3 | Fear of Success |
>>> lyrics/seemsill.php3 | Seems I'll | Lyrics |
>>> mp3/php3/SeemsIll20031029_ds.php3 | Wasted Tears |
>>> mp3/php3/seemsill_ds.php3 | Seems I'll | Song |
>>> mp3/mp3s/seemsill.mp3 | Wasted Tears |
>>> mp3/php3/SeemsIll20031029_ds.php3 | Seems I'll | Song |
>>> mp3/mp3s/SeemsIll20031029.mp3 | Fear of Success |
>>> mp3/php3/SeemsIllUnplugged_ds.php3 | Seems I'll | Song |
>>> mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | n/a |
>>> +------------------------------------+------------+--------+----------
>>>
>>> Here is the query in question (I'm trying to get the count of all
>>> versions of Seems I'll songs, which was originally on the CD Wasted
>>> Tears, so I can display the other versions, including the one on
>>> Wasted Tears {mp3/mp3s/seemsill.mp}):
>>> SELECT file_details.Title,
>>> file_details.Type,
>>> 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, file_details.Type, file_details_1.CD,
>>> file_details_1.mp3Name ORDER BY file_details_1.Title;
>>>
>>> But, oddly here is the result of this query:
>>> +------------+------+-----------------+-------------------------
>>> Title | Type | CD |
>>> mp3Name | cnt |
>>> +------------+------+-----------------+-------------------------
>>> Seems I'll | Song | Fear of Success |
>>> mp3/mp3s/SeemsIll20031029.mp3 | 2 |
>>> Seems I'll | Song | n/a |
>>> mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | 2 |
>>> Seems I'll | Song | Wasted Tears |
>>> mp3/mp3s/seemsill.mp3 | 2 |
>>> +------------+------+-----------------+-------------------------
>>>
>>> The count should be 3, right? What gives?
>>>
>>>
>>
>> The cnt field looks to be correct here based on your data. The count
>> here is the number of results in that grouping not the total number
>> of rows returned. If you remove the group by clause from your query,
>> you should see 6 rows returned (2 of each).
Regards,
--
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
