SQL - SELECTING from one table while INNER JOINING between two others (many-to-many table and anothe-CodePudding

I am a beginner to Web programming and SQL. I am not used to work with tables that have a many to many relationship and I am facing a problem here due to my lack of knowledge.

Those are my tables and this is what I want to do:

Table users

ID | Users
-----------------
1  | John
2  | Mark
3  | Sophia

Table projects

ID | Projects
-----------------
1  | Generic Name nº 1
2  | Generic Name nº 2
3  | Generic Name nº 3

Table users_projects

UsersID | ProjectsID
-----------------
    1   |    1
    2   |    1
    2   |    2
    3   |    2
    3   |    3

I want to select all the users where, let's say, the Projects.Id value is 1, while keeping the many to many realtionship between this two tables. How do I do that?

Desired Output

ID | Users
-----------------
1  | John
2  | Mark

CodePudding user response：

SELECT t1.*
FROM users t1
JOIN users_projects t2 ON t1.id = t2.usersid

SELECT *
FROM users t1
WHERE EXISTS ( SELECT NULL
               FROM users_projects t2 
               WHERE t1.id = t2.usersid )

I recommend you to rename ID columns in users and projects and assign them the same names which are used in users_projects. This will remove ambiguity and will make your structure and queries more clear.

CodePudding user response：

If you want the result for Projects.Id value is 1 try:

select u.ID,u.Users
from users u
inner join users_projects p on u.ID=p.UsersID
where p.ProjectsID=1;



CREATE TABLE  users (
 ID int,
 Users varchar(10) );

INSERT INTO users VALUES
(1,'John'),
(2,'Mark'),
(3,'Sophia');

CREATE TABLE  users_projects (
 UsersID int,
 ProjectsID int );

INSERT INTO users_projects VALUES
(1,1),
(2,1),
(2,2),
(3,2),
(3,3);

Result:

ID  Users
1   John
2   Mark

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=463a1cfac69f07d41a2caa440decdb25

CodePudding user response：

You can try below, by using left join you specify on which fields to join. You can use table aliases and prefix the fields with the alias to indicate between which table the fields are linked.

select u.Users, u.ID, p.Projects, p.ID
from Users u
left join user_project up
on u.ID = up.UsersID
left join projects p
on up.ProjectsID = p.projects
order by u.Users, p.Projects