CODEWITHSUNDEEP
mysqlsqldatabaseselect
Blane Townsend
Blane Townsend

Reputation: 3038

SQL select users that belong to the same object

I have a MySQL database set up where I have a User, Project, and ProjectUser table. The ProjectUser table has a UserID and ProjectID and maps a user to a project. I want to be able to query which projects two different users have in common. So if Bob belongs to projects A, B, C, D and Joe belongs to projects C, D, E, F... then I want to SELECT the information for projects C and D when I know the UserID of Bob and Joe.

Upvotes: 0

Views: 227

Answers (2)

softdevlife
softdevlife

Reputation: 98

Assume this is the metadata and the examples you provided.

create table User        (UserID    int, Name varchar(200));
create table Project     (ProjectID int, Name varchar(200), Information varchar(200));
create table ProjectUser (UserID    int, ProjectID int    );

INSERT INTO User        VALUE (1,'Bob'),(2,'Joe');
INSERT INTO Project     VALUE (1,'A','First'),(2,'B','Second'),(3,'C','Third'),(4,'D','Fourth'),(5,'E','Fifth'),(6,'F','Sixth');
INSERT INTO ProjectUser VALUE (1,1),(1,2),(1,3),(1,4),(2,3),(2,4),(2,5),(2,6);

Assuming the records in ProjectUser table are unique by UserID and ProjectID, you can get the details of projects Bob and Joe share by:

SELECT Project.* FROM Project WHERE Project.ProjectID IN
(
   SELECT Project.ProjectID 

   FROM ProjectUser 

   INNER JOIN User
   ON User.UserID       = ProjectUser.UserID

   INNER JOIN Project
   ON Project.ProjectID = ProjectUser.ProjectID

   WHERE User.Name IN ('Bob','Joe')

   GROUP BY Project.ProjectID 

   HAVING COUNT(*) > 1
)

Try it on http://sqlfiddle.com/

Upvotes: 1

Mahmoud
Mahmoud

Reputation: 883

you can select projects for userId1 and userId2 separately and finally by intersecting them you will find projects which have these two users!!! For SQL server

select * from projects where PID IN
(select PID from ProjectUser PU1 where UserId = UserID1 
INTERSECT 
select PID from ProjectUser PU2 where UserId = UserID2)

and for mySql you write as follow:

select * from projects where PID IN
    (select PID from ProjectUser PU1 where UserId = UserID1 and  
            PID In (select PID from ProjectUser PU2 where UserId = UserID2))

Upvotes: 0

Related Questions