sql server one to many join

I am using SQLServer 2008 and I have two tables - one with a list of files with an associated ID, and the other with a list of authors with an associated FILE_ID. In the authors table, there can be many authors with the same FILE_ID. I want to query both tables and return records with all of the authors associated with a fi开发者_开发知识库le in one record

I can't see any way to do that. And I think the reason there is no apparent way to do it is that it violates the whole concept of relational design. Without knowing what your reasoning is for wanting a single record returned, I can only suggest that you rethink your approach.

It's usually useful to give sample data and anticipated outcomes; it sounds like you're looking for a concatenation solution, which would be something like:

DECLARE @files TABLE (ID int, fName varchar(100))
DECLARE @authors TABLE (ID int, fileID int, aName varchar(100))

INSERT INTO @files (ID, fName)
VALUES (1, 'file1'), (2, 'file2')

INSERT INTO @authors (ID, fileID, aName)
VALUES (1, 1, 'author1'),
        (2, 1, 'author2'),
        (3, 1, 'author3'),
        (4,2, 'author4')


SELECT fName,
    authors = STUFF((SELECT ',' + aName FROM @authors a WHERE a.FileID = f.ID FOR XML PATH('')), 1, 1, '') 
FROM @files f