Use selection inside of a second selection

Transact-SQL
,
1

Hello,

I have the following selection:

> SELECT CONCAT('Applicant's Name: ',AppID, CHAR(13) + CHAR(10)+'Application Date: ',AppDate,
> CHAR(13) + CHAR(10)+'The products are: ',@Products) AS Body
> FROM tblApplication
>  WHERE Appno=@Appno

Products is not a member of tblApplictions but should be a list returned from the following:
> DECLARE @Products AS NVARCHAR(1000)
SELECT @Products = ProductDescr
> FROM tblProducts INNER JOIN tblApplication
ON tblProducts.AppID=tblApplication.AppID
> WHERE Appno=@Appno

The problem is I only get one record of products. Any kind of help would be appreciated.

Thank you in advance.

2

please provide sample table definition and sample data.It would be good if you can create table variable and insert script for that and post the same here.

3

provide sample code as follows

declare @Appno varchar(50)
set @Appno = 'Chrisz'

create table #tblProducts(Appid int, ProductDescr varchar(50))
create table #tblApplication(AppId int, Appno varchar(50), AppDate date)
insert into #tblApplication
select 1, 'Slice Vader', '2012-01-01' union
select 2, 'Anti Matta', '2013-01-01' union
select 3, 'Time Travel', '2014-01-01' union
select 4, 'Chrisz', '2015-01-01'


insert #tblProducts
select 1, 'Schuks Absorbers' union
select 1, 'Light Saber' union
select 1, 'Flacon' union
select 4, 'Jedi Force' union
select 4, 'Nut Crackers'

--CONCAT ( string_value1, string_value2 [, string_valueN ] ) 
SELECT CONCAT('Applicant''s Name: ', ta.AppID, CHAR(13) + CHAR(10) + 'Application Date: ',AppDate,
CHAR(13) + CHAR(10)+'The products are: ', ProductDescr) AS Body

FROM #tblProducts tp
  INNER JOIN #tblApplication ta
ON tp.AppID=ta.AppID
WHERE Appno=@Appno

drop table #tblProducts
drop table #tblApplication
4 
SELECT CONCAT('Applicant''s Name: ', ta.AppID, CHAR(13) + CHAR(10) + 'Application Date: ',AppDate,
       CHAR(13) + CHAR(10)+'The products are: ', stuff( (SELECT ','+ ProductDescr 
               FROM #tblProducts tp
               WHERE ta.AppId = tp.AppId
               ORDER BY ProductDescr
               FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
            ,1,1,'')) AS Body
      FROM #tblApplication ta
WHERE Appno=@Appno
1 Like
5

Thank you yosiasz for providing the code and the solution for me.
It works perfectly :slight_smile:

6

spoonfeeding is not good idea,

7

@nextaxtion is there a more efficient way to make that scenario work?