Page 1 of 1

Easy... but something is wrong?!!?

Posted: Thu Jan 05, 2006 2:47 pm
by seodevhead
Why is this returning the wrong COUNT of table1?

'table1' has a total of 5 records.

Query:
"SELECT COUNT(table1.id) FROM table1, table2 WHERE registered='Y'";

Note: registered is a column in table2.

This query is returning the COUNT as 29 records??? But there are only 5 records total in table1??? How could this be? Thanks!

Posted: Thu Jan 05, 2006 3:39 pm
by RobertPaul
If all you want is the record count of table1 then why not just do SELECT COUNT(*) from table1 ... AFAIK you're not (usually) supposed to put anyththing in COUNT() other than an asterisk. Otherwise things get funky. That's obviously not the technical explaination but it escapes me at the moment...

Posted: Thu Jan 05, 2006 4:06 pm
by seodevhead
Well... in a sense all I really want is records from table1... but I had to include table2 so I could add 'register' in the WHERE clause.

Posted: Thu Jan 05, 2006 4:10 pm
by seodevhead
[SOLVED] I'm just going to use mysql_num_rows().

Re: Easy... but something is wrong?!!?

Posted: Thu Jan 05, 2006 4:48 pm
by timvw
seodevhead wrote:Why is this returning the wrong COUNT of table1?

'table1' has a total of 5 records.

Query:
"SELECT COUNT(table1.id) FROM table1, table2 WHERE registered='Y'";

Note: registered is a column in table2.

This query is returning the COUNT as 29 records??? But there are only 5 records total in table1??? How could this be? Thanks!
Do you know what happens when you select from more than one table?
Do you know what happens if you specifiy a column as argument in an aggregate function?

If not, it's up to you to do some research.