Status: Solved
Priority: Medium
Security: Public
Views: 255
Last Modified: 2012-03-18

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.

arthurh88

Asked: 2012-03-18

Advertise Here

1 Solution

LVL 7

micropc1Commented: 2012-03-18

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

LVL 39

appariCommented: 2012-03-18

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

arthurh88Author Commented: 2012-03-18

that was it! I knew it was something easy

Question has a verified solution.

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

help with my sql query select in a select

Join & Write a Comment Already a member? Login.

Featured Post