CODEWITHSUNDEEP
sql
user2945318
user2945318

Reputation: 59

sql query with many to many relationship

We have many-to-many Relationship and these are the details for the same and its non-hierarchical data.

For a User name test 1 >> find Team >> And the find others Members of the Team

 Table Team (Team Table) 
    Id Name 
    1  Ninja
    2  Maverick
    3  Transformer

 Table User (User Table)
    Id Name Email
    1  test1 [email protected]
    2  test2 [email protected]
    3  test3 [email protected]

 Table Team_USER_Map (User Map)
    ID User_Id Team ID
    1  1       1
    2  1       2
    3  2       3
    4  2       1
    5  3       1
    6  3       2
    7  3       3

Problem Statement:

Depending upon the User Name, I need to find all other's user for same Team.

So for user name = 'test1', I need to find the Team its belongs and other user belonging to same team.

Upvotes: 0

Views: 145

Answers (1)

arman1991
arman1991

Reputation: 1166

Try this: 

  DECLARE @Name as nvarchar (50)      
  SET @Name = 'test1'

    SELECT u.Name as UserName, u.Email, t.Name as TeamName
    FROM Team as t INNER JOIN Team_USER_Map as tum ON t.Id = tum.Team_ID 
    INNER JOIN User as u ON u.Id = tum.User_Id 
    WHERE u.Name LIKE @Name OR tum.Team_ID = 
    (
      SELECT t.Id 
      FROM Team as t INNER JOIN Team_USER_Map as tum ON t.Id = tum.Team_ID 
      INNER JOIN User as u ON u.Id = tum.User_Id 
      WHERE u.Name LIKE @Name 
    )

I think that is what you want to achieve. The first part of WHERE clause is easy because it returns the user with it's name.

In second part, you must use SELECT WITHIN SELECT statement to get all users which belong to team of user of typed name.

I hope it's helpful and clear enough.

Good luck!

Upvotes: 1

Related Questions