Trouble Calling Function In View && Multi-Table Select...

How Do I fix View(below) or Multi-Table select(below) to use this Function to return distinct rows via qcParent_ID?

Following Function populates a field (with concat list of related titles) with other required fields:

Create Function [dbo].openItemsIntoList(@Delimeter varchar(15),@qcparent_ID varchar(1000))

Returns Varchar(8000) as

Begin

Declare @Lists as varchar(8000);



Select @Lists = '';

Select @Lists = @Lists + itemTitle + @Delimeter From z_QClocate_openAll_Qualifier

Where @qcParent_ID = qcParent_ID;

Return Substring(@Lists,1,len(@Lists)-len(@Delimeter));

End

works perfect against single table select (returning 54 distinct rows by qcParent_ID):

Select a.qcParent_ID, a.Facility, a.Modality, openItemListToFix

From dbo.a2_qcEntryForm a

JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i

on a.qcParent_ID = i.qcParent_ID

But data is needed from 3 tables...
- Created a VIEW that returns all (82) rows (negating distinct of the function on qcParent_ID)
- Failed Miserably Integrating Function call into a multi-table select (inexperienced with complex joins)


This VIEW returns ALL (82) rows in table:


CREATE VIEW z_QClocate_openAll AS

SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,

dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality

FROM dbo.a1_qcParent INNER JOIN

dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN

dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND

dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID

WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR

(dbo.a1_qcParent.qcStatus = 'In Process') OR

(dbo.a1_qcParent.qcStatus = 'Re-Opened')

Calling like this returns ALL 82 rows (negating the functions distinct):

Select a.qcParent_ID, a.qcStatus, a.qcAlert, a.itemComplete, a.Facility, a.Modality, openItemListToFix

From z_QClocate_openAll a

JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i

on a.qcParent_ID = i.qcParent_ID

AND THEN THERES...
Failing miserably on Integrating the Function call into This SELECT ON MULTI-TABLES:

How to integrate the Function call:
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i

on a.qcParent_ID = i.qcParent_ID

into the multi-table Select relationships (while maintaining Where & Order By):

SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,

dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality

FROM dbo.a1_qcParent INNER JOIN

dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN

dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND

dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID

WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR

(dbo.a1_qcParent.qcStatus = 'In Process') OR

(dbo.a1_qcParent.qcStatus = 'Re-Opened')