?
Solved

SQL 2008 Union and sort question

Posted on 2011-03-17
5
Medium Priority
?
442 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 1200 total points
ID: 35163089
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
ID: 35163169
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 800 total points
ID: 35164290
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 1200 total points
ID: 35165975
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
ID: 35169456
Fantastic  - all great answers. All work.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

777 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