January 20, 2015 at 11:46 pm
Hey, I haven't used SQL very much and it's been a few years and I'm trying to wrap my head around a concept.
Basically I've got two tables with a common column (in this case it's UserID)
The structure of the table Users is basically:
UserID FirstName LastName
1 Jim Smith
2 Nancy Orr
3 Ken Stephenson
And the structure of the classes table is
UserID ClassID
1 AAA
1 BBB
2 AAA
3 BBB
What I'd like to do is select the FirstName and LastName the users table who have a UserID in both Classes and Users.
The tricky part of it is that I only want to return the FirstName and LastName of userID who have multiple ClassID in the classes table. (hopefully I'm making sense when I explain this)
In this case I'd like it to return just Jim Smith's name because he's the only userID who has two ClassID entries.
So far I've come up with
select UserId, count(*) as MultipleClasses
from classes
group by UserID
having count(*) > 1;
which outputs the UserID and a colum called MultipleClasses with how many classes each ID returned has.
how would I do something like
Select firtname, lastname
from users
where Userid= The output from that initial bit finding users with multiple classes
I'm definitely willing to scrap everything I've come up with so far as I feel like I'm probably barking up the wrong tree here, but any help would be appreciated!
January 21, 2015 at 12:11 am
If I understand what you are looking for, I think this will do it :
Select firtname, lastname
from users
where Userid in (select UserId
from classes
group by UserID
having count(*) > 1)
January 21, 2015 at 12:34 am
Thank you, yeah that was exactly what I needed.
I tried basically exactly this but I was using a "=" instead of "in" and it was throwing an error.
Thanks again, I appreciate it
January 21, 2015 at 3:38 am
I am glad I could help 🙂
You can use = (SELECT... only when you are sure the subquery returns only one row, in other cases you must use IN
Roland
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply