displaying the rows
-
I have a sql query
SELECT PropertyName, PropertyValue, UserID FROM ProfilePropertyDefinition
INNER JOIN UserProfile ON
ProfilePropertyDefinition.PropertyDefinitionID = UserProfile.PropertyDefinitionID
which displays the results like this
propertyName propertyValue userid
FirstName Jon 1
LastName Doe 1
CompanyName yahoo 1
FirstName Mark 2
lastName doe 2
CompanyName MSN 2
The results i want to show are like this
FirstName Lastname CompanyName USerid
jon Doe yahoo 1
mark doe msn 2
Does any 1 can help with this
-
a great example of the headaches the EAV model causes. What should be a simple select turns into a messy pivot. What do you do if you need to do a join? I would give up now.
Here's several articles on crosstab queries:
http://weblogs.sqlteam.com/jeffs/category/156.aspx
and this article should convince you not to use EAV:
http://weblogs.sqlteam.com/davidm/articles/12117.aspx
and this thread on sqlteam echoes my opinion on the matter:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57307
---------------------------------------
elsasoft.org -
While I agree with jezemine about the model, below is a solution (untested) that should work for you. Keep in mind that you didn't qualify columns, so I guessed as to where they came from. I guessed UserProfile as your main source of data, and that the ProfilePropertyDefinition table is somehow used to limit your data to those particular property types. If that's not the case, either tweak it yourself, or give us more detail about how things are structured. Note also that used last name as the left most table, assuming that that probably had a higher likelihood of being populated than the others. Change this as well if your data differs.
SELECT
FirstName = f.propertyValue
,LastName = l.propertyValue
,CompanyName = c.propertyValue
,USerid = l.userid
FROM
(
SELECT
userid
,propertyValue
FROM
UserProfile
WHERE
propertyName = 'LastName'
) AS l
LEFT OUTER JOIN
(
SELECT
userid
,propertyValue
FROM
UserProfile
WHERE
propertyName = 'FirstName'
) AS f ON
l.userid = f.userid
LEFT OUTER JOIN
(
SELECT
userid
,propertyValue
FROM
UserProfile
WHERE
propertyName = 'CompanyName'
) AS c ON
l.userid = c.userid
ORDER BY
USerid
-
that's to be compared with this query, which is what you would have had if not using EAV:
select FirstName,Lastname,CompanyName,UserID from User
which do you prefer?
---------------------------------------
elsasoft.org -
Thanks for ur help
-
One view could solve all your problems with joins.
_____________
Code for TallyGenerator -
A view that looks like the following would certainly do it (untested)...
SELECT d.FirstName = MIN(CASE WHEN d.PropertyName = 'FirstName' THEN d.PropertyValue ELSE '' END),
d.LastName = MIN(CASE WHEN d.PropertyName = 'LastName' THEN d.PropertyValue ELSE '' END),
d.CompanyName = MIN(CASE WHEN d.PropertyName = 'CompanyName' THEN d.PropertyValue ELSE '' END),
d.UserID
FROM (--Derived table "d" contains your original query
SELECT PropertyName, PropertyValue, UserID
FROM ProfilePropertyDefinition ppd
INNER JOIN UserProfile up
ON ppd.PropertyDefinitionID = up.PropertyDefinitionID
) d
GROUP BY d.UserID
ORDER BY d.UserID
Wouldn't be a write-able view though...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
one view will NOT solve all the problems that EAV creates however.
---------------------------------------
elsasoft.org -
I don't see any problems with EAV for those who does not use to use SELECT *
In poperly designed application you need to return FirstName, LastName, etc. once per person. Once, no more. And view will do it easily.
And for search purposes EAV solves problems of multiple column scanning, when you perform something like this:
WHERE Address1 like '%Main%' or Address2 like '%Main%'
Not to mention flexibility of handling new types of properties.
_____________
Code for TallyGenerator -
It would appear that a simple query like this would solve the problem far more efficiently:
SELECT
MAX (CASE PropertyName WHEN 'FirstName' THEN PropertyValue END) AS FirstName,
MAX (CASE PropertyName WHEN 'LastName' THEN PropertyValue END) AS LastName,
MAX (CASE PropertyName WHEN 'CompanyName' THEN PropertyValue END) AS CompanyName,
UserID
FROM
ProfilePropertyDefinition PPD
INNER JOIN
UserProfile UP
ON
PPD.PropertyDefinitionID = UP.PropertyDefinitionID
GROUP BY
UserID
-
's'ok... great minds think alike... I went a little nuts with the derived table thingy, though
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
A EVA data model creates huge problems for those who are unwise enough to fall into the trap: No data integrity on data types, no referential integrity, no way to create practical indexes. And that just for starters. Then you get to write the most horrible looking queries ever seen, and spend hours watching a query run that would take a few seconds with a relational model. Then you get to explain to everyone why the report shows the product color is 500 pounds, and the weight is $49.99.
Basically, it gives up every benefit of the relational model.
Sorry, it is basically a repeat of what Jeff had above.Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply