Merger a column in to a single cell

[glasgowlad1999]

Can someone help me with this query issue.

The SQL below allows me to merger a column in to a single cell based on FacID, only if there is more than 1 provider. How can I get it to also return FacIDs with only 1 provider?

Example
FacID Provider
1 Jane Smith
1 John Doe
1 Jane Smith
1 Jane Smith
1 Jane Smith
1 Jane Smith

It returns:
FacID Provider
1 Jane Smith, John Doe

However if I had:
FacID Provider
1 Jane Smith
1 John Doe
1 Jane Smith
1 Jane Smith
1 Jane Smith
1 Jane Smith
2 John Doe
2 John Doe
2 John Doe
2 John Doe

It returns:
FacID Provider
1 Jane Smith, John Doe
It doesn't return: 2

However if I had:
FacID Provider
1 Jane Smith
1 John Doe
1 Jane Smith
1 Jane Smith
1 Jane Smith
1 Jane Smith
2 John Doe
2 John Doe
2 John Smith
2 John Doe
2 John Doe

It returns:
FacID Provider
1 Jane Smith, John Doe
2 John Doe, John Smith

SELECT [%$##@_Alias].FacID, Max([%$##@_Alias].authors) AS MaxOfauthors
FROM (SELECT dbo_tblupload.FacID, dbo_tblupload.Provider & ", " & dbo_tblupload2.Provider AS Authors FROM dbo_tblupload INNER JOIN dbo_tblupload AS dbo_tblupload2 ON (dbo_tblupload.Provider < dbo_tblupload2.Provider) AND (dbo_tblupload.FacID = dbo_tblupload2.FacID)) AS [%$##@_Alias]
GROUP BY [%$##@_Alias].FacID;

Thanks

 

[namliam]

Change your INNER JOIN to a LEFT JOIN

 

[glasgowlad1999]

Thanks. Why did that make the difference?

 

[namliam]

Try reading all this to find out:
https://www.google.nl/#q=the+difference+between+inner+and+outer+join