asked on

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

John Tsioumpris

Just delete the criteria

ASSET_ID = 806046

Open in new window

skij

ASKER

The problem is that "806046" occurs in many places. This is a combination of 6 queries. I can't delete that number in just one single place and also maintain the integrity of the results. I think I need a subquery or to somehow combine the queries into one, but I don't know how to do that.

John Tsioumpris

I have to read it more carefully...

Vitor Montalvão

You didn't specify the table schemas so I'm guessing only:

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)
DECLARE @TAGLIST nvarchar(max)
DECLARE @SERVICESLIST nvarchar(max)
DECLARE @ILLUSTLIST nvarchar(max)
DECLARE @MEDIALIST 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)),
  @TAGLIST = COALESCE(@TAGLIST +' ','') + LTRIM(RTRIM(t2.TAGNAME)), 
  @SERVICESLIST = COALESCE(@SERVICESLIST +' ','') +  LTRIM(RTRIM(z2.KeyName)), 
  @ILLUSTLIST = COALESCE(@ILLUSTLIST +' ','') +  LTRIM(RTRIM(z2.KeyName)),
  @MEDIALIST = COALESCE(@MEDIALIST +' ','') +  LTRIM(RTRIM(z2.KeyName))
FROM IPM_ASSET a
  INNER JOIN IPM_USER usert ON a.UserID = usert.UserID
  INNER JOIN IPM_ASSET_TAG t1
	INNER JOIN IPM_TAG t2 ON t1.TAGID = t2.TAGID
	ON a.ASSET_ID = t1.ASSET_ID
  INNER JOIN IPM_ASSET_SERVICES z1
	INNER JOIN IPM_SERVICES z2 ON z1.KeyID = z2.KeyID AND z3.KEYUSE = 1
	ON a.ASSET_ID = z1.ASSET_ID 
  INNER JOIN IPM_ASSET_ILLUSTTYPE z3
	INNER JOIN IPM_ILLUSTTYPE z4 ON z3.KeyID = z4.KeyID AND z3.KEYUSE = 1
	ON a.ASSET_ID = z3.ASSET_ID 
  INNER JOIN IPM_ASSET_MEDIATYPE z5
	INNER JOIN IPM_MEDIATYPE z6 ON z5.KeyID = z6.KeyID AND z5.KEYUSE = 1
	ON a.ASSET_ID = z5.ASSET_ID 
WHERE a.ASSET_ID = 806046
	AND TAGUSE = 1 AND LEN(t2.TAGNAME) BETWEEN 3 AND 99
	

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

Open in new window

skij

ASKER

@Vitor Montalvão, your query also only returns one row. I don't want 806046 to be referenced at all. Maybe I need a subquery to loop through all rows and not just the one row 806046?

John Tsioumpris

Dump the last line from Vitor's solution

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

skij

ASKER

I need that last line because it is the line that returns the results I need. Yet because of that last line only one row is ever released.

@Vitor Montalvão, your most recent post only returns one single row.

John Tsioumpris

If you want this resolved i guess a sample of data along with table structuring is needed..

Vitor Montalvão

your most recent post only returns one single row.

That's because you're trying to store the information into variables. A variable can't handle more than a value. You need to be more specific with your requirements so we can help you better.
Follow a version without variables:

SELECT 
  LTRIM(RTRIM(ASSET_ID)), LTRIM(RTRIM(a.Name)), LTRIM(RTRIM(a.Description)), a.FileName, LTRIM(RTRIM(a.Search_Text)), LTRIM(RTRIM(usert.FirstName)), LTRIM(RTRIM(usert.LastName)),
  LTRIM(RTRIM(t2.TAGNAME)), LTRIM(RTRIM(z2.KeyName)), LTRIM(RTRIM(z2.KeyName)), LTRIM(RTRIM(z2.KeyName))
FROM IPM_ASSET a
  INNER JOIN IPM_USER usert ON a.UserID = usert.UserID
  INNER JOIN IPM_ASSET_TAG t1
	INNER JOIN IPM_TAG t2 ON t1.TAGID = t2.TAGID
	ON a.ASSET_ID = t1.ASSET_ID
  INNER JOIN IPM_ASSET_SERVICES z1
	INNER JOIN IPM_SERVICES z2 ON z1.KeyID = z2.KeyID AND z3.KEYUSE = 1
	ON a.ASSET_ID = z1.ASSET_ID 
  INNER JOIN IPM_ASSET_ILLUSTTYPE z3
	INNER JOIN IPM_ILLUSTTYPE z4 ON z3.KeyID = z4.KeyID AND z3.KEYUSE = 1
	ON a.ASSET_ID = z3.ASSET_ID 
  INNER JOIN IPM_ASSET_MEDIATYPE z5
	INNER JOIN IPM_MEDIATYPE z6 ON z5.KeyID = z6.KeyID AND z5.KEYUSE = 1
	ON a.ASSET_ID = z5.ASSET_ID 
WHERE TAGUSE = 1 AND LEN(t2.TAGNAME) BETWEEN 3 AND 99

Open in new window

ASKER CERTIFIED SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial