So I am in a bit of a jam and dont know how to do this.

I have a search screen where people will enter textboxes to search a parent table. But this parent table has a child table that it can have a one to many relationship with. The search screen needs to be able to return the parents reference if a field of the child is searched.

So the tables are

Investigation -> Parent
Clients -> reference/Child



So from the search screen there are say 4 text boxes.
Poeple can do a search for Field1, Field2 as well as ClientNumber or ClientName.

I would like to return

InvestigationID, Field1, Field2, ClientNumber, ClientName.

My SQL looks like this. But I am not doing it right because I did a join. What I need to do is a subquery but I dont know how.

SELECT matters.matterID AS matterID,
IsNull(dbo.udf_getClientMatter(matters.matterID), '') AS clientMatter,
IsNull(matters.title, '') AS Title,
IsNull(descriptionSubject,'') AS Description,
IsNull((Select RTrim(statusDescription) FROM status WHERE status.statusID = matters.statusID ), '') AS Status,
IsNull((Select RTrim(lastActionDescription) FROM lastAction WHERE lastAction.lastActionID = Matters.lastActionID ), '') AS [Last Action],
IsNull(CONVERT(char(19), dateOpened, 101), '') AS dateOpened,
IsNull(CONVERT(char(19), dateOfLastAction, 101), '') AS dateOfLastAction,
IsNull(dbo.udf_getCaseTypes(matters.matterID), '') AS caseTypes,
IsNull(dbo.udf_getForumTypes(matters.matterID), '') AS forumTypes
FROM Matters
FROM matters JOIN clients
ON clients.matterID = matters.matterID
ORDER BY matterID, dateOpened DESC


This return 4 results for my investigation when its supposed to return 1 becase I have 4 clients connected to my investigation table.

Any help would be appreciated.