Solved: MS SQL: Getting all rows not just one , combining multiple queries

This returns only on result, the result where ASSET_ID = 806046.

Using MS SQL, how can I get all the results, for all ASSET_ID items?

DECLARE @aID varchar(40)
DECLARE @pID varchar(40)
DECLARE @aNAME nvarchar(max)
DECLARE @aDESCRIPTION nvarchar(max)
DECLARE @aFILENAME nvarchar(max)
DECLARE @aSEARCHTEXT nvarchar(max)
DECLARE @aFIRSTNAME nvarchar(max)
DECLARE @aLASTNAME nvarchar(max)

SELECT 
  @aID = LTRIM(RTRIM(ASSET_ID)), @aNAME = LTRIM(RTRIM(a.Name)), @aDESCRIPTION = LTRIM(RTRIM(a.Description)), @aFILENAME = a.FileName, @aSEARCHTEXT = LTRIM(RTRIM(a.Search_Text)), @aFIRSTNAME = LTRIM(RTRIM(usert.FirstName)), @aLASTNAME = LTRIM(RTRIM(usert.LastName))
  FROM IPM_ASSET a
  INNER JOIN IPM_USER usert ON a.UserID = usert.UserID
    WHERE 
	ASSET_ID = 806046

DECLARE @TAGLIST nvarchar(max)
SELECT 
  TOP 60
  @TAGLIST = COALESCE(@TAGLIST +' ','') + LTRIM(RTRIM(t2.TAGNAME))
  FROM IPM_ASSET_TAG t1
  INNER JOIN IPM_TAG t2 ON t1.TAGID = t2.TAGID
    WHERE 
	ASSET_ID = 806046
	AND TAGUSE = 1
	AND LEN(t2.TAGNAME) > 2
	AND LEN(t2.TAGNAME) < 100

DECLARE @SERVICESLIST nvarchar(max)
SELECT 
  TOP 50
  @SERVICESLIST = COALESCE(@SERVICESLIST +' ','') +  LTRIM(RTRIM(z2.KeyName))
  FROM IPM_ASSET_SERVICES z1
  INNER JOIN IPM_SERVICES z2 ON z1.KeyID = z2.KeyID
    WHERE 
	ASSET_ID = 806046
	AND KEYUSE = 1

DECLARE @ILLUSTLIST nvarchar(max)
SELECT 
  TOP 50
  @ILLUSTLIST = COALESCE(@ILLUSTLIST +' ','') +  LTRIM(RTRIM(z2.KeyName))
  FROM IPM_ASSET_ILLUSTTYPE z1
  INNER JOIN IPM_ILLUSTTYPE z2 ON z1.KeyID = z2.KeyID
    WHERE 
	ASSET_ID = 806046
	AND KEYUSE = 1

DECLARE @MEDIALIST nvarchar(max)
SELECT 
  TOP 50
  @MEDIALIST = COALESCE(@MEDIALIST +' ','') +  LTRIM(RTRIM(z2.KeyName))
  FROM IPM_ASSET_MEDIATYPE z1
  INNER JOIN IPM_MEDIATYPE z2 ON z1.KeyID = z2.KeyID
    WHERE 
	ASSET_ID = 806046
	AND KEYUSE = 1

SELECT 806046 as ASSET_ID, CONCAT(@aID, ' ', @pID, ' ',  @aNAME, ' ' , @aFILENAME, ' ',  @aDESCRIPTION, ' ', @aSEARCHTEXT , ' ', @TAGLIST, ' ', @SERVICESLIST, ' ', @ILLUSTLIST, ' ', @MEDIALIST, ' ', @aFIRSTNAME, ' ', @aLASTNAME)

Open in new window