[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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.
0
arthurh88
Asked:
arthurh88
1 Solution
 
micropc1Commented:
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now