Solved

SQL 2008 Union and sort question

Posted on 2011-03-17
5
430 Views
Last Modified: 2012-06-22
Hey there.
I have query to a "user profile" table that returns different information depending on the "User Role" - I achieve this by using a UNION.. Other factors:
All users are in the FREE role
Some users are also in the LIGHT or FULL role
The result are then ordered by ROLE priority

The issue:
I need to be able to NOT display an item from the FREE role if it is also in the FULL or LIGHT role. Currently it displays both.
Is there also a more streamlined way to run the attached query?


USE [qef_beta-1]
GO
/****** Object:  StoredProcedure [quickbeta].[populateNetismMapExtreme_Cached]    Script Date: 03/18/2011 12:54:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--- create Stored Procedure

ALTER PROCEDURE [quickbeta].[populateNetismMapExtreme_Cached]
AS
BEGIN

truncate table [quickbeta].[NetismMapExtreme_Cached]

Insert into [quickbeta].[NetismMapExtreme_Cached]

select ISNULL(m.TableName,'Users') AS TableName,
       ISNULL(m.ID1, u.UserID) AS ID1,
	   u.userid as LocationID, 
	   u.displayname as Title,
	   '' as description, 
	   u.email as link, 
	   u.FirstName as FirstName, 
       m.Latitude, m.Longitude, m.Zoom, m.Iconfile,
       (ISNULL (up10.PropertyValue,'') + ' ' + up1.PropertyValue) as Address,
       up2.PropertyValue as City,
       up3.PropertyValue as State,
       up4.PropertyValue as Zip,
       up5.PropertyValue as Country,
       up6.PropertyValue as Image,
       up7.PropertyValue as Website,
	   up8.PropertyValue as Category,
	   up9.PropertyValue as Subcate,
	   up10.PropertyValue as Unit,
	   up11.PropertyValue as Bio,
	   up12.PropertyValue as telephone,
	   ISNULL(up13.PropertyValue,0) as Feature,
	   r.RoleID as Roles,
	   h.RoleName as RoleName,
	   RoleValue = 
		CASE 
			WHEN h.RoleName = 'Business Full' THEN 3
			ELSE 0
		END
	   
from  Users u
join UserRoles r on u.UserID = r.UserID
join UserPortals p on u.UserID = p.UserID and p.PortalID = 0
join Roles h on r.RoleID = h.RoleID AND h.RoleName = 'Business Full'
left join UserProfile up1 on up1.UserID = u.UserID and up1.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Street')
left join UserProfile up2 on up2.UserID = u.UserID and up2.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'City')
left join UserProfile up3 on up3.UserID = u.UserID and up3.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Region')
left join UserProfile up4 on up4.UserID = u.UserID and up4.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'PostalCode')
left join UserProfile up5 on up5.UserID = u.UserID and up5.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Country')
left join UserProfile up6 on up6.UserID = u.UserID and up6.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Image')
left join UserProfile up7 on up7.UserID = u.UserID and up7.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Website')
left join UserProfile up8 on up8.UserID = u.UserID and up8.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Category')
left join UserProfile up9 on up9.UserID = u.UserID and up9.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Subcate')
left join UserProfile up10 on up10.UserID = u.UserID and up10.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Unit')
left join UserProfile up11 on up11.UserID = u.UserID and up11.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Bio')
left join UserProfile up12 on up12.UserID = u.UserID and up12.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Telephone')
left join UserProfile up13 on up13.UserID = u.UserID and up13.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Feature')
left join Netism_MapExtreme_AttachedLocations AS m on m.TableName = 'Users' and u.UserID = m.ID1

WHERE up8.PropertyValue <> ''
AND
up9.PropertyValue <> ''
AND
up1.PropertyValue IS NOT NULL
AND
up1.PropertyValue <> ''
AND
(r.ExpiryDate !< GetDate() or r.ExpiryDate is NUll) 

UNION

select ISNULL(m.TableName, 'Users') AS TableName,
       ISNULL(m.ID1, u.UserID) AS ID1,
	   u.userid as LocationID, 
	   u.displayname as Title,
	   '' as description, 
	   u.email as link, 
	   u.FirstName as FirstName, 
       m.Latitude, m.Longitude, m.Zoom, m.Iconfile,
       (ISNULL (up10.PropertyValue,'') + ' ' + up1.PropertyValue) as Address,
       up2.PropertyValue as City,
       up3.PropertyValue as State,
       up4.PropertyValue as Zip,
       up5.PropertyValue as Country,
       '' as Image,
       '' as Website,
	   up8.PropertyValue as Category,
	   up9.PropertyValue as Subcate,
	   up10.PropertyValue as Unit,
	   '' as Bio,
	   '' as telephone,
	   ISNULL(up13.PropertyValue,0) as Feature,
	   r.RoleID as Roles,
	   h.RoleName as RoleName,
	   RoleValue = 
		CASE 
			WHEN h.RoleName = 'Business Light' THEN 2
			ELSE 0
		END
	   
from  Users u
join UserRoles r on u.UserID = r.UserID
join UserPortals p on u.UserID = p.UserID and p.PortalID = 0
join Roles h on r.RoleID = h.RoleID AND h.RoleName = 'Business Light'
left join UserProfile up1 on up1.UserID = u.UserID and up1.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Street')
left join UserProfile up2 on up2.UserID = u.UserID and up2.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'City')
left join UserProfile up3 on up3.UserID = u.UserID and up3.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Region')
left join UserProfile up4 on up4.UserID = u.UserID and up4.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'PostalCode')
left join UserProfile up5 on up5.UserID = u.UserID and up5.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Country')
left join UserProfile up6 on up6.UserID = u.UserID and up6.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Image')
left join UserProfile up7 on up7.UserID = u.UserID and up7.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Website')
left join UserProfile up8 on up8.UserID = u.UserID and up8.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Category')
left join UserProfile up9 on up9.UserID = u.UserID and up9.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Subcate')
left join UserProfile up10 on up10.UserID = u.UserID and up10.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Unit')
left join UserProfile up11 on up11.UserID = u.UserID and up11.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Bio')
left join UserProfile up12 on up12.UserID = u.UserID and up12.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Telephone')
left join UserProfile up13 on up13.UserID = u.UserID and up13.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Feature')
left join Netism_MapExtreme_AttachedLocations AS m on m.TableName = 'Users' and u.UserID = m.ID1

WHERE up8.PropertyValue <> ''
AND
up9.PropertyValue <> ''
AND
up1.PropertyValue IS NOT NULL
AND
up1.PropertyValue <> ''
AND
(r.ExpiryDate !< GetDate() or r.ExpiryDate is NUll)

UNION

select ISNULL(m.TableName, 'Users') AS TableName,
       ISNULL(m.ID1, u.UserID) AS ID1,
	   u.userid as LocationID, 
	   u.displayname as Title,
	   '' as description, 
	   '' as link, 
	   u.FirstName as FirstName, 
       m.Latitude, m.Longitude, m.Zoom, m.Iconfile,
       (ISNULL (up10.PropertyValue,'') + ' ' + up1.PropertyValue) as Address,
       up2.PropertyValue as City,
       up3.PropertyValue as State,
       up4.PropertyValue as Zip,
       up5.PropertyValue as Country,
       '' as Image,
       '' as Website,
	   up8.PropertyValue as Category,
	   up9.PropertyValue as Subcate,
	   up10.PropertyValue as asd,
	   '' as Bio,
	   '' as telephone,
	   ISNULL(up13.PropertyValue,0) as Feature,
	   r.RoleID as Roles,
	   h.RoleName as RoleName,
	   RoleValue = 
		CASE 
			WHEN h.RoleName = 'Business Free' THEN 1
			ELSE 0
		END
	   
from  Users u
join UserRoles r on u.UserID = r.UserID
join UserPortals p on u.UserID = p.UserID and p.PortalID = 0
join Roles h on r.RoleID = h.RoleID AND h.RoleName = 'Business Free'
left join UserProfile up1 on up1.UserID = u.UserID and up1.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Street')
left join UserProfile up2 on up2.UserID = u.UserID and up2.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'City')
left join UserProfile up3 on up3.UserID = u.UserID and up3.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Region')
left join UserProfile up4 on up4.UserID = u.UserID and up4.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'PostalCode')
left join UserProfile up5 on up5.UserID = u.UserID and up5.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Country')
left join UserProfile up6 on up6.UserID = u.UserID and up6.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Image')
left join UserProfile up7 on up7.UserID = u.UserID and up7.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Website')
left join UserProfile up8 on up8.UserID = u.UserID and up8.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Category')
left join UserProfile up9 on up9.UserID = u.UserID and up9.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Subcate')
left join UserProfile up10 on up10.UserID = u.UserID and up10.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Unit')
left join UserProfile up11 on up11.UserID = u.UserID and up11.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Bio')
left join UserProfile up12 on up12.UserID = u.UserID and up12.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Telephone')
left join UserProfile up13 on up13.UserID = u.UserID and up13.PropertyDefinitionID = dbo.GetProfilePropertyDefinitionID(0, 'Feature')
left join Netism_MapExtreme_AttachedLocations AS m on m.TableName = 'Users' and u.UserID = m.ID1

WHERE 
up8.PropertyValue <> ''
AND
up9.PropertyValue <> ''
AND
up1.PropertyValue IS NOT NULL
AND
up1.PropertyValue <> ''
AND
(r.ExpiryDate !< GetDate() or r.ExpiryDate is NUll)

END

Open in new window

0
Comment
Question by:flyingsquirel
  • 2
  • 2
5 Comments
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 300 total points
Comment Utility
The query looks good, but and you're already returning their role level as an INT, so it should be pretty straightforward. Can you select these results to a temp table and then just select all the columns with MAX(RoleValue) as their new RoleValue? If they're "Free" (0), it would return 0, but if they're "Free" (0) and "Full" (2), it would return 2.

Also, you may get some slightly better performance by changing your UNION statements to UNION ALL. It can return additional rows if the same row appears in multiple queries, but since every query returns unique rows, you'll benefit from the switch without any adverse effects.
0
 
LVL 4

Author Comment

by:flyingsquirel
Comment Utility
That all sounds GOOD, Im a novice SQL user so Please clarify how and where I would use the MAX and how to use the UNION ALL with the query above.

I have that query populating a table. I then reference that table for the report with.

SELECT *
  FROM [qef_beta-1].[quickbeta].[NetismMapExtreme_Cached]
ORDER BY
 RoleValue DESC,Feature DESC,LocationID ASC
0
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 200 total points
Comment Utility
Hi,
May be this is the query that you should use for your Report:

SELECT a.*
FROM [quickbeta].[NetismMapExtreme_Cached] a ,
(SELECT LocationID, MAX(RoleValue) RoleValue FROM [quickbeta].[NetismMapExtreme_Cached] GROUP BY LocationID) b
WHERE a.LocationID = b.LocationID and a.RoleValue = b.RoleValue
ORDER BY RoleValue DESC,Feature DESC,LocationID ASC

PS: I have considered that the LocationID is the userid that is common across all Unions.
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 300 total points
Comment Utility
That's correct - you'd essentially use your query as-is, and then to fetch the data for your report, you'd use the query that rajeev has provided.

This will still leave duplicate rows in your table, but if that's okay (this report is the only thing that reads from it, for example), then no more work is necessary.

Also, you could use the query as-is to populate the table, but then delete rows from the table when the user has multiple packages (keeping the highest-value package):

DELETE [quickbeta].[NetismMapExtreme_Cached]
  FROM [quickbeta].[NetismMapExtreme_Cached] a
  LEFT
  JOIN (SELECT LocationID, MAX(RoleValue) RoleValue FROM [quickbeta].[NetismMapExtreme_Cached] GROUP BY LocationID) b
    ON a.LocationID = b.LocationID
   AND a.RoleValue = b.RoleValue 
 WHERE b.LocationID IS NULL

Open in new window


This will populate the entire table, and then delete rows where the package isn't the highest value package for the person - when they're FULL, it will remove LITE and FREE rows for that person, for example. Then your report will be just a SELECT * from the table with your ORDER BY however you want it.
0
 
LVL 4

Author Comment

by:flyingsquirel
Comment Utility
Fantastic  - all great answers. All work.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now