CODEWITHSUNDEEP
phpmysqljoin
max_
max_

Reputation: 24481

Inner Join using COUNT(*)

I am selecting the user's information from my MySQL database as shown below:

SELECT * FROM `Users` WHERE `$user_or_id` = ?

However, I would like to add an extra bit off information to the returned data. The extra bit of data is the total number of records in a table named 'Venues' where the rows' field, 'user_id' is the same as the 'id' field in the table, 'Users'.

Please can you tell me where I am going wrong with the following query? Here is the error I am receiving:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) FROM Users AS u INNER JOIN Venues AS v ON u.id = v.user_id WHERE u.id = '' at line 1

SELECT u.*, v.count(*) FROM `Users` AS u INNER JOIN `Venues` AS v ON u.id = v.user_id WHERE u.$user_or_id = ?

Upvotes: 2

Views: 1824

Answers (3)

KernelM
KernelM

Reputation: 8916

Just use count(*) instead of v.count(*).

Upvotes: 1

Interrobang
Interrobang

Reputation: 17434

SELECT u.*, COUNT(v.*) FROM `Users` AS u INNER JOIN `Venues` AS v ON u.id = v.user_id WHERE u.$user_or_id = ?

COUNT is a MySQL function, not a member of table v. Pass it an argument representing what you want to count-- in this case, v's rows.

Upvotes: 4

Marc B
Marc B

Reputation: 360632

It should be COUNT(v.*). Otherwise it's interpreted as "function count inside table V", which isn't valid.

Upvotes: 1

Related Questions