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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

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

Post 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!
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

What do you mean it just doesn't work? Using COUNT(*) is perfectly acceptable using inner joins.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
Post Reply