help with my sql query select in a select

Select UserID, (select count(*) from names where userid = userid) as totalcount from users where firstname like '%john%'


The error is "where userid = userid".  I don't know how to word this.  UserID's are unique accross both tables.  I want to return results for all firstnames like "%john%" out of my Users table, and also, for each record, I want the count(*) from names for that same UserID.
arthurh88Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
micropc1Connect With a Mentor Commented:
You need an alias for the outer table...

Select u.UserID, (select count(*) from names where userid = u.userid) as totalcount 
from users u
where u.firstname like '%john%'

Open in new window

0
 
appariCommented:
or try like this

Select users.UserID, count(names.userid) as totalcount
from users left outer join names
on users.userid = names.userid
where firstname like '%john%'
Group by users.UserID
0
 
arthurh88Author Commented:
that was it!  I knew it was something easy
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.