Multiple Unions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple Unions

Author

Topic

mad marcus
Starting Member

12 Posts

Posted - 2009-11-26 : 23:40:06

Hi,

I am trying to construct a view to pull data from 2 tables. The first contains a userid and the second contains a number of key | value pairs for each userid. The trouble I am having is getting the values from the second table assigned to individual columns in the view. Here is what I am using:

SELECT dbo.Users.UserID, dbo.Users.FirstName + ' ' + dbo.Users.LastName AS ContactName, dbo.Users.Email, UserProfile_Union.Postcode

FROM dbo.Users FULL OUTER JOIN
(SELECT UserID, PropertyValue AS Postcode
FROM dbo.UserProfile
WHERE (PropertyDefinitionID = 30)
UNION
SELECT UserID, PropertyValue AS AddressState
FROM dbo.UserProfile AS UserProfile_1
WHERE (UserProfile_1.PropertyDefinitionID = 42)
UNION
SELECT UserID, PropertyValue AS AddressCity
FROM dbo.UserProfile AS UserProfile_2
WHERE (PropertyDefinitionID = 27)
)
AS UserProfile_Union ON dbo.Users.UserID = UserProfile_Union.UserID
WHERE (Users.IsDeleted <> 1)

This returns the right data and the first 3 columns are correct showing UserID, ContactName and Email but I can't get a 4th, 5th and 6th column only a 4th column that display multiple rows for each UserID.

I know it is something simple I am missing but I get an error when I try to add something like UserProfile_Union.AddressState or UserProfile_1.AddressState to the first select statement.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-27 : 01:30:50

If you add those columns you need to add in other statements too

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-27 : 02:20:45

probably this is what you want

SELECT dbo.Users.UserID, dbo.Users.FirstName + ' ' + dbo.Users.LastName AS ContactName, dbo.Users.Email, UserProfile_Union.Postcode,
UserProfile_Union.AddressState,
UserProfile_Union.AddressCity
FROM dbo.Users FULL OUTER JOIN
(SELECT UserID, 
MAX(CASE WHEN PropertyDefinitionID = 30 THEN  PropertyValue ELSE NULL END) AS Postcode,
MAX(CASE WHEN PropertyDefinitionID = 42 THEN  PropertyValue ELSE NULL END) AS AddressState,
MAX(CASE WHEN PropertyDefinitionID = 27 THEN  PropertyValue ELSE NULL END) AS AddressCity
FROM dbo.UserProfile
GROUP BY UserID
) 
AS UserProfile_Union ON dbo.Users.UserID = UserProfile_Union.UserID
WHERE (Users.IsDeleted <> 1)

mad marcus
Starting Member

12 Posts

Posted - 2009-11-27 : 02:49:03

quote:

Originally posted by visakh16

probably this is what you want

SELECT dbo.Users.UserID, dbo.Users.FirstName + ' ' + dbo.Users.LastName AS ContactName, dbo.Users.Email, UserProfile_Union.Postcode,
UserProfile_Union.AddressState,
UserProfile_Union.AddressCity
FROM dbo.Users FULL OUTER JOIN
(SELECT UserID, 
MAX(CASE WHEN PropertyDefinitionID = 30 THEN  PropertyValue ELSE NULL END) AS Postcode,
MAX(CASE WHEN PropertyDefinitionID = 42 THEN  PropertyValue ELSE NULL END) AS AddressState,
MAX(CASE WHEN PropertyDefinitionID = 27 THEN  PropertyValue ELSE NULL END) AS AddressCity
FROM dbo.UserProfile
GROUP BY UserID
) 
AS UserProfile_Union ON dbo.Users.UserID = UserProfile_Union.UserID
WHERE (Users.IsDeleted <> 1)

Thanks visakh16 that is exactly what a needed and more efficient than what I was trying to do and I never would have got that on my own

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources