Which SQL function can solve my query? Combine multiple tables

Question

I have 3 tabels (Files,Users,Files_Users)

Files Table A

Id	File Name
1	A
2	B
3	C
4	D
5	E

Users Table B

Id	User Name	is_internal	is_external
1	Adam	1	1
2	john	1	0
3	Caren	0	1

Files_Users tabel

Id	File_Id	Int_User_Id	Ext_User_Id
1	1	1	1
2	2	2	1
3	3	1	3

I want to show all files related to user_id = 1 only (to fill ComboBox in C# WinForms) my Approach was :

select 
A.Id,
File_Name 
from 
Files A,
Users B
where 
A.Id = B.File_Id and
Ext_Emp_Id = 1 OR Int_Emp_Id = 1

but it display all the five files in files table and repeatedly,

I expected to show the 1st 3 files names only because user_id = 1 showed in Files_Users in the 3 rows

please any help is appreciated

Answer 1

Use the explicit join syntax. Then this should do it:

select f.Id, f.Name 
from Files f
join Files_Users fu on fu.File_Id = f.id
join Users u on u.id in (fu.Int_User_Id, fu.Ext_User_Id)
where u.id = 1