DataSource Controls :: Concatenating Rows From Another Two Tables

I have a problem where I would like to get a comma-separated list of items from a couple of tables as part of a set of results. All the searching I've done so far has produced results for dealing with one or two tables. I have four, and can't get my head around how to adapt the sql to deal with it.

I have the following layout:

*Table_Castings*
+---- CastingID {PK}
| MouldID

| SerialNo

| CastRef

| MeltCode

| Signature

|

|

| *Table_CastCertificates* *Table_Certificates*

| CertificateID {PK} ----------- CertificateID {PK}

+---- CastingID {PK} Description

FileID -----------------+

|

|

*Table_CertificateFiles* |

FileID {PK} -------------+

FullLocation

The results I would like include the following columns:

CastingID, MouldID, SerialNo, CastRef, MeltCode, [Certificates],

Where Certificates is a comma separated list of Description + " " + FullLocation records. (What I'll actually do is add http:// and other html codes to make FullLocation a hyperlink, but I can do that.)

Some SQL I've already got is this:

DECLARE @str VARCHAR(500)

SELECT @str = COALESCE(@str + ', ','') + '<a href="http://' + Table_CertificateFiles.FullLocation + '">' + Table_Certificates.Description + '</a>'

FROM Table_CastCertificates INNER JOIN

Table_Certificates ON Table_CastCertificates.CertificateID = Table_Certificates.CertificateID INNER JOIN

Table_CertificateFiles ON Table_CastCertificates.FileID = Table_CertificateFiles.FileID

WHERE (CastingID = 45)

SELECT output = @str

This actually gives all records in the for the specific casting, but I keep getting an error at the "=" in @str = COALESCE when I try joining it to another query to get each casting.