Solved

SQL Separate and lookup value - follow up question

Posted on 2011-03-19
2
302 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

740 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