Re: ODD COUNT(*) Results on Self-Join (Bug?)
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.
Best Regards,
Van
=
http://www.dedserius.com/-Linux rocks!!!
=
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;
RE: ODD COUNT(*) Results on Self-Join (Bug?)
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 |
Re: ODD COUNT(*) Results on Self-Join (Bug?)
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]
ODD COUNT(*) Results on Self-Join (Bug?)
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?
Thanks,
Van
=
http://www.dedserius.com/-Linux rocks!!!
=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ODD COUNT(*) Results on Self-Join (Bug?)
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]
