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,
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
