SQL Separate and lookup value - follow up question

Hi,

cyberkiwi answered my provious question, but since then I need to modify this slightly to filter only the relevant records for the particular companyid, I have attached a spreadsheet to show a sample of the data.

so with the following sp:
@id int,
@CompanyID varchar

AS
BEGIN

SET NOCOUNT ON;

select a.id, v.value, b.DocumentReference, '<a href="uploads''' + @CompanyID + '''/' + b.filename + '">' + b.filename + '</a>' As LinkFile

from tbl_permits a
cross apply dbo.values2table(a.MethodStatements, ',') v
inner join tbl_documents b on b.DocumentReference=v.value
where a.id=@id

END

If ID = 23 and CompanyID=18

the the returned value should be:

MS001      MS001      <a href="uploads18/MS 14 Document.pdf">MS001</a>

alternatively,
If ID = 3 and CompanyID=1

the the returned value should be:
MS001      MS001      <a href="uploads1/MS 1 Document.pdf">MS001</a>
MS002      MS002      <a href="uploads1/MS 2 Document.pdf">MS002</a>
MS003      MS003      <a href="uploads1/MS 3 Document.pdf">MS003</a>

How this makes sense.

 Book1.xls Book1.xls
sanjshah12Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
interesting, your attached files has tbl_Documents and tbl_main as table names, your query has tbl_permit and tbl_documents ...

so, I presume the companyid is the one from tbl_Documents ...

@id int,
@CompanyID varchar

AS
BEGIN

SET NOCOUNT ON;

select a.id, v.value, b.DocumentReference, '<a href="uploads''' + @CompanyID + '''/' + b.filename + '">' + b.filename + '</a>' As LinkFile

from tbl_permits a
cross apply dbo.values2table(a.MethodStatements, ',') v 
inner join tbl_documents b on b.DocumentReference=v.value AND b.companyID = @CompanyID
where a.id=@id

END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sanjshah12Author Commented:
Thanks angelIII, Yes I had to change the code hence the slight change, anyway, I tried something similar but could not get it work, but your code works.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.