CODEWITHSUNDEEP
sqlsql-server
user2061217
user2061217

Reputation:

Select only same values as other row has

I do not know exactly how to name this topic, so I will try to explain it. I have two tables in MS SQL database - Sports and UsersSports. In Sport table I have list of sports. In UsersSports table I assign sports to users - every user can have more than one sport.

CREATE TABLE [dbo].[Sports](
[SportId] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
[Sport] [nvarchar](max) NOT NULL)

CREATE TABLE [dbo].[UsersSports](
    [UserSportId] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
    [SportId] [int] NOT NULL REFERENCES [Sports]([SportId]),
    [ProfileId] [int] NOT NULL REFERENCES [Profiles]([ProfileId]))

I want to select user's sports, but only sports, which other user has assigned in UsersSports table. For example, I play football and basketball and other user plays basketball and tennis, so command should select only basketball, because we both play basketball.

I can only select user's sports by this command:

SELECT Sports.SportId, Sport FROM Sports JOIN UsersSports ON Sports.SportId = UsersSports.SportId WHERE UsersSports.ProfileId = '1003'

So how can I achieve this? Thanks

Upvotes: 2

Views: 93

Answers (1)

obourgain
obourgain

Reputation: 9336

You can use a SQL:EXIST condition.

Here is a sample:

SELECT Sports.SportId, Sport
FROM   Sports
JOIN   UsersSports
ON     Sports.SportId = UsersSports.SportId
WHERE  UsersSports.ProfileId = '1003'
AND EXISTS
    (SELECT 1 
     FROM   UsersSports AS otherUser
     WHERE  otherUser.ProfileId != '1003'  
     AND    otherUser.SportID = Sports.SportId
    )

Upvotes: 3

Related Questions