CODEWITHSUNDEEP
sqlsql-server
Joshua Kemmerer
Joshua Kemmerer

Reputation: 1673

How can I return rows from an SQL query where one unique ID satisfies conditions in different rows?

I have a table users which has a foreign key in my other table, usersHaveSocialMediaFans.

usersHaveSocialMediaFans has columns:

socialMediaId, userId, fanCount

I need to find all users that have a fan count greater than (or less than) a certain amount for each social media. Admins need to be able to filter out all users that might have at least 6 Facebook fans AND 7 Twitter followers.

It was super easy when I had this information in my user table:

userId, ..., facebookFans, twitterFollowers, ...

---0--, ---, -----10-----, ------1000------, ...

Then, I could just SELECT userId from users WHERE facebookFans > # AND twitterFollowers > #; however, now my data for each user is split up into multiple rows and I don't know how to handle the logic. I feel like there must be a simple answer, but I just can't think of it right now.

Upvotes: 1

Views: 41

Answers (2)

BobNoobGuy
BobNoobGuy

Reputation: 1645

One way to do it

SELECT U.userId, F.SocialMediaID, F.fanCount from users U 
LEFT JOIN usersHaveSocialMediaFans F
ON F.Userid = U.Userid
WHERE (F.SocialMediaID='Facebook' and Fancount>1000) 
And (F.SocialMediaID='Twitter' and Fancount>1000)

another way to do it:

SELECT U.userId, F.fanCount "FacebookFan", t.fanCount "TwitterFan" from users U 
LEFT JOIN usersHaveSocialMediaFans F 
ON F.Userid = U.Userid and F.SocialMediaID= 'Facebook'
LEFT JOIN usersHaveSocialMediaFans T
ON T.Userid = U.Userid and T.SocialMediaID= 'Twitter'
WHERE F.FanCount > 1000 and T.FanCount > 1000

Upvotes: 2

Siyual
Siyual

Reputation: 16917

You just need to JOIN to your usersHaveSocialMediaFans table twice for it. For example, if you wanted those with more than 1,000 Facebook fans, and more than 10 Twitter fans, you could do the following:

Select  Distinct U.UserId
From    Users                       U
Join    usersHaveSocialMediaFans    F   On  F.UserId = U.UserId 
                                        And F.socialMediaId = <FacebookSocialMediaId>
Join    usersHaveSocialMediaFans    T   On  T.UserId = U.UserId
                                        And T.socialMediaId = <TwitterSocialMediaId>
Where   F.fanCount > 1000
And     T.fanCount > 10;

If you have another table that stores the name of the SocialMediaId for each platform, you should do another JOIN there instead of hard-coding in the SocialMediaId's for each platform. But I don't know your schema, so hopefully this will point you in the right direction.

If, hypothetically, you had another table named SocialMedia that had the SocialMediaId and Name of each platform, you could do the following:

Select  Distinct U.UserId
From    Users                       U
Join    usersHaveSocialMediaFans    F   On  F.UserId = U.UserId 
Join    SocialMedia                 SF  On  SF.SocialMediaId = F.SocialMediaId
                                        And SF.Name = 'Facebook'
Join    usersHaveSocialMediaFans    T   On  T.UserId = U.UserId
Join    SocialMedia                 ST  On  ST.SocialMediaId = T.SocialMediaId
                                        And ST.Name = 'Twitter'
Where   F.fanCount > 1000
And     T.fanCount > 10;

Hopefully this helps!

Upvotes: 1

Related Questions