Link to home
Start Free TrialLog in
Avatar of flyingsquirel
flyingsquirelFlag for New Zealand

asked on

SQL 2008 Union and sort question

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

SOLUTION
Avatar of Ryan McCauley
Ryan McCauley
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of flyingsquirel

ASKER

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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Fantastic  - all great answers. All work.