Hi
Sorry if I'm askin this in the wrong forum, this is the best I could find.
I would like to use a module called antamap to show a map with makers for the members of our site, thought that would be an easy task, NOT so.
I thought all user addresses and stuff would be in one or two tables and straight forward, but there is more to it than just a join of two tables.
I have managed to make a list of most of the "data" I would like, however I'm stuck now, I need the properties to become "fictive fields" and with the "PropertyName" as the "columnname" e.g. Street, telephone, postalcode etc. so it should kind of add some extra columns instead of having n rows.
SELECT
Users.UserID, Users.Username, Users.FirstName, Users.LastName, Users.DisplayName, UserProfile.PropertyValue, .Visibility, UserProfile.LastUpdatedDate, ProfilePropertyDefinition.PropertyCategory, .PropertyName
FROM
Users INNER JOIN
UserProfile
ON Users.UserID = UserProfile.UserID INNER JOIN
ProfilePropertyDefinition
ON UserProfile.PropertyDefinitionID = ProfilePropertyDefinition.PropertyDefinitionID
WHERE
(Users.Username = 'Pamela')
The result looks like this (sorry about the format, couldn't upload a screen dump)
UserID Username FirstName LastName DisplayName PropertyValue Visibility LastUpdatedDate PropertyCategory PropertyName
27 Pamela Pamela Anderson Pam 0 2 2007-01-01 22:49:37.783 Stamdata Medlemsnummer
27 Pamela Pamela Anderson Pam 1 2 2007-01-01 22:49:37.800 Fødselsdag Dag
27 Pamela Pamela Anderson Pam 7 2 2007-01-01 22:49:37.800 Fødselsdag Måned
27 Pamela Pamela Anderson Pam 1967 2 2007-01-01 22:49:37.800 Fødselsdag År
27 Pamela Pamela Anderson Pam Pamela 2 2007-01-01 22:49:37.800 Name FirstName
27 Pamela Pamela Anderson Pam Anderson 2 2007-01-01 22:49:37.800 Name LastName
27 Pamela Pamela Anderson Pam 60 2 2007-01-01 22:49:37.800 Preferences TimeZone
27 Pamela Pamela Anderson Pam da-DK 2 2007-01-01 22:49:37.800 Preferences PreferredLocale
27 Pamela Pamela Anderson Pam Hollywood Boulevard 2 2007-01-01 23:18:02.610 Address Street
27 Pamela Pamela Anderson Pam 5800 2 2007-11-29 22:37:12.557 Address PostalCode
27 Pamela Pamela Anderson Pam Los Angeles 2 2007-01-01 23:18:02.610 Address City
27 Pamela Pamela Anderson Pam Denmark 2 2007-01-01 23:18:02.610 Address Country
(12 row(s) affected)
It is made by the query designer in SQL management studio, and I'm a newbie to this,
UserProfile
ProfilePropertyDefinition