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 combo box in c sharp winfroms)
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
>Solution :
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