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).
--
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]
