Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Separate and lookup value - follow up question

Posted on 2011-03-19
2
Medium Priority
?
307 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:sanjshah12
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35173029
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
 

Author Comment

by:sanjshah12
ID: 35173455
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question