Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

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

Avatar of skij
skijFlag for Canada asked on
Microsoft SQL Server
11 Comments1 Solution87 ViewsLast Modified:
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)
ASKER CERTIFIED SOLUTION
Avatar of John Tsioumpris
John TsioumprisFlag of Greece imageIT Supervisor
Commented:
This problem has been solved!
Unlock 1 Answer and 11 Comments.
See Answers