Page 1 of 1

Using Count(*) with an inner join...

Posted: Sun May 28, 2006 4:20 pm
by GeXus
If im joining two tables, both of which im using a count(*) on... how would i do t his? I cant just do Table1.count(*) , Table2.count(*).. it just doesnt seem to work... any idea how this would be done?

Thanks!

Posted: Sun May 28, 2006 4:35 pm
by twigletmac
Use COUNT() on a specific field instead of using *? It's only a guess ('cause it's late) but worth a try :)

Mac

Posted: Sun May 28, 2006 5:03 pm
by John Cartwright
What do you mean it just doesn't work? Using COUNT(*) is perfectly acceptable using inner joins.

Posted: Sun May 28, 2006 8:34 pm
by GeXus
Jcart wrote:What do you mean it just doesn't work? Using COUNT(*) is perfectly acceptable using inner joins.

So your saying that the following should work?

Code: Select all

SELECT Table1.ID, Table2.ID, Table1.count(*), Table2.count(*) From Table1 Inner Join Table2 on Table1.ID = Table2.ID

Posted: Sun May 28, 2006 8:49 pm
by John Cartwright

Code: Select all

SELECT `Table1`.`ID`, `Table2`.`ID`, COUNT(*) as `count` FROM `Table1` INNER JOIN `Table2` ON `Table1`.`ID` = `Table2`.`ID`
Give that a try