Left Join Issue

Question

I've got a 1 to many relationship between two tables. I thought a Left join would give me 1 row, but it's giving me all the rows! Should I do Select Top 1? I'm wanting as many rows that exist in ui to be returned from my query, not the opposite, and have the data from ui returned for each row in data

Create Table ui
(
 ID int IDENTITY(1,1) PRIMARY KEY,
 username varchar(500),
 fileid varchar(max)
)

Create Table data
(
  ID int IDENTITY(1,1) PRIMARY KEY,
  fileid varchar(max)
)

Insert Into ui (username, fileid) Values ('abc-123', '1.xlsx'), ('lmn-123', '2.xlsx')
Insert Into data (fileid) Values ('1.xlsx'), ('1.xlsx'), ('1.xlsx'), ('1.xlsx'), ('1.xlsx'), 
('1.xlsx'), ('1.xlsx'), ('1.xlsx'), ('1.xlsx'), ('1.xlsx'), 
('1.xlsx'), ('1.xlsx'), ('1.xlsx'), ('1.xlsx'), ('1.xlsx'), 
('1.xlsx'), ('1.xlsx'), ('2.xlsx')

Select ui.*
FROM ui
left join data 
on ui.fileid = data.fileid

Answer 1

If you need only 1 row from data to match 1 row from ui, then you need to use OUTER APPLY and select top (1) approach and order by some column, say, ID, e.g.

select ui.*, d.*
from ui
OUTER APPLY (select top (1) * from data d where d.FileId = ui.FileId order by d.Id) D