SQL Query question

Question

Hi All,

I have two SQL queries.

select BMEs.DisplayName AS 'Review Activity', BMEt.DisplayName as 'Reviewer'

from BaseManagedEntity as BMEs, BaseManagedEntity as BMEt, Relationship as R, 
RelationshipType as RT 

where BMEs.BaseManagedEntityId = R.SourceEntityId and 

BMEt.BaseManagedEntityId = R.TargetEntityId and 

RT.RelationshipTypeId = R.RelationshipTypeId and

RelationshipTypeName = 'System.ReviewActivityHasReviewer'

Which outputs the Review Activity and the user ID.

I have a second query for the ReviewerID and ReviewerName.

select BMEs.DisplayName as ReviewerID, BMEt.DisplayName as ReviewerName

from BaseManagedEntity as BMEs, BaseManagedEntity as BMEt, Relationship as R, RelationshipType as RT 

where BMEs.BaseManagedEntityId = R.SourceEntityId and 

BMEt.BaseManagedEntityId = R.TargetEntityId and 

RT.RelationshipTypeId = R.RelationshipTypeId 

and RT.RelationshipTypeName = 'System.ReviewerIsUser'

Which outputs the ID and the Name of the reviewer.

Is it possible to join both queries and output Review Activity, ReviewerID and ReviewerName.

Hope someone can help me out with this one.

Thanks in advance.

Bart Timmermans

---

Bart Timmermans | Consultant at inovativ
Follow me @ My Blog | Linkedin | Twitter

Please mark as Answer, if my post answers your Question. Vote as Helpful, if it is helpful to you.

Answer 1

You can try this approach:

SELECT * FROM 
(select BMEs.DisplayName AS 'Review Activity', BMEt.DisplayName as 'Reviewer'

from BaseManagedEntity as BMEs, BaseManagedEntity as BMEt, Relationship as R, 
RelationshipType as RT 

where BMEs.BaseManagedEntityId = R.SourceEntityId and 

BMEt.BaseManagedEntityId = R.TargetEntityId and 

RT.RelationshipTypeId = R.RelationshipTypeId and

RelationshipTypeName = 'System.ReviewActivityHasReviewer') AS T1,
(select BMEs.DisplayName as ReviewerID, BMEt.DisplayName as ReviewerName

from BaseManagedEntity as BMEs, BaseManagedEntity as BMEt, Relationship as R, RelationshipType as RT where BMEs.BaseManagedEntityId = R.SourceEntityId and BMEt.BaseManagedEntityId = R.TargetEntityId and RT.RelationshipTypeId = R.RelationshipTypeId and RT.RelationshipTypeName = 'System.ReviewerIsUser') AS T2
WHERE
T1.Reveiewer = T2.ReveiewerID

---

Shatrughna.

Answer 2

Absolutely; however, I would prefer that you struggle a bit more with this first.  Also, you need to do some research into a topic: "EAV tables" and how to use them.

EDIT:

The easiest way to visualize your question is expressed in Naomi's response that follows.

Answer 3

You can use the above two queries as CTE and then join them based on ReviewerID.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

Hi Shkumar,

Thank your for your reply. Works excellent. However do you know if it is possible to hide the T1.Reviewer and T2.Reviewer afterwards?

Kind regards,

Bart Timmermans

@Kent and Naomi: Thanks I will do some more research on EAV tables and CTE.

---

Bart Timmermans | Consultant at inovativ
Follow me @ My Blog | Linkedin | Twitter

Please mark as Answer, if my post answers your Question. Vote as Helpful, if it is helpful to you.

Answer 5

Hi Shkumar,

Thank your for your reply. Works excellent. However do you know if it is possible to hide the T1.Reviewer and T2.Reviewer afterwards?

Kind regards,

Bart Timmermans

@Kent and Naomi: Thanks I will do some more research on EAV tables and CTE.

---

Bart Timmermans | Consultant at inovativ
Follow me @ My Blog | Linkedin | Twitter

Please mark as Answer, if my post answers your Question. Vote as Helpful, if it is helpful to you.

For that you can replace SELECT * in the beginning with the required list of columns from T1 and/or T2 aliases (or derived tables) {something like SELECT T1.[Review Activity], T1.Reviewer, T2.ReviewerName FROM ...}.

Thanks!

Answer 6

Just one more question. I currently have the following query:

SELECT T1.[Review Activity], T3.Workitem, T2.ReviewerName From
(select BMEs.DisplayName AS 'Review Activity', BMEt.DisplayName as 'ReviewerID'

from BaseManagedEntity as BMEs, BaseManagedEntity as BMEt, Relationship as R, 
RelationshipType as RT 

where BMEs.BaseManagedEntityId = R.SourceEntityId and 

BMEt.BaseManagedEntityId = R.TargetEntityId and 

RT.RelationshipTypeId = R.RelationshipTypeId and

RelationshipTypeName = 'System.ReviewActivityHasReviewer') AS T1,
(select BMEs.DisplayName as ReviewerID, BMEt.DisplayName as ReviewerName

from BaseManagedEntity as BMEs, BaseManagedEntity as BMEt, Relationship as R, RelationshipType as RT where BMEs.BaseManagedEntityId = R.SourceEntityId and BMEt.BaseManagedEntityId = R.TargetEntityId and RT.RelationshipTypeId = R.RelationshipTypeId and RT.RelationshipTypeName = 'System.ReviewerIsUser') AS T2,

(select BMEs.DisplayName as Workitem, BMEt.DisplayName as 'Review Activity'

from BaseManagedEntity as BMEs, BaseManagedEntity as BMEt, Relationship as R, RelationshipType as RT where BMEs.BaseManagedEntityId = R.SourceEntityId and BMEt.BaseManagedEntityId = R.TargetEntityId and RT.RelationshipTypeId = R.RelationshipTypeId and BMEt.BaseManagedTypeID = 'BFD90AAA-80DD-0FBB-6EAF-65D92C1D8E36') AS T3,

/* Voted By */
(select BMEs.DisplayName as VoterID, BMEt.DisplayName as ReviewerName

from BaseManagedEntity as BMEs, BaseManagedEntity as BMEt, Relationship as R, RelationshipType as RT where BMEs.BaseManagedEntityId = R.SourceEntityId and BMEt.BaseManagedEntityId = R.TargetEntityId and RT.RelationshipTypeId = R.RelationshipTypeId and RT.RelationshipTypeName = 'System.ReviewerVotedByUser') AS T4

WHERE
T1.ReviewerID = T2.ReviewerID and
T1.[Review Activity] = T3.[Review Activity] and
T1.ReviewerID = T4.VoterID

ORDER BY T1.[Review Activity]

This will output the following:

These are all review activities that are approved. This is based on the RT.RelationshipTypeName = 'System.ReviewerVotedByUser' in T4. What I would like to achieve is to retrieve only the users that hasn't voted. Allready <> instead of the =. But that does not work.

Is this possible?

Thanks in advance.

Kind regards,

Bart Timmermans

---

Bart Timmermans | Consultant at inovativ
Follow me @ My Blog | Linkedin | Twitter

Please mark as Answer, if my post answers your Question. Vote as Helpful, if it is helpful to you.

Answer 7

Take a look at NOT EXISTS subquery to filter users that haven't voted.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

We do not format display data in the database. This looks like you have an EAV disaster. This is where the non-SQL programmer builds tables with meta data and constructs his tables on the fly in such a way as to have no underlying data model or any idea what the data looks like. Google it and find out the painful details. 

“entity_id” in RDBMS?! So OO, so meta-data!  I hope you fired the moron that did this to you. Even if you get this fragile design to hold together (based on a few decades of experience, I give them about 1 to 2 years max) the performance decays exponentially and it is nearly impossible to write any thing more complex than SELECT..FROM.. WHERE.. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 9

We do not format display data in the database. This looks like you have an EAV disaster. This is where the non-SQL programmer builds tables with meta data and constructs his tables on the fly in such a way as to have no underlying data model or any idea what the data looks like. Google it and find out the painful details. 

“entity_id” in RDBMS?! So OO, so meta-data!  I hope you fired the moron that did this to you. Even if you get this fragile design to hold together (based on a few decades of experience, I give them about 1 to 2 years max) the performance decays exponentially and it is nearly impossible to write any thing more complex than SELECT..FROM.. WHERE.. 

Thank you so much for picking me up on this, Joe. 

Bart:

Please pay attention to these warnings.  There might be some situations in which use of EAV tables work out decently -- yours is probably one of them.  However, there are also many "EAV disaster" situations out there; you want to make sure that your situation isn't one of them!

peace out.

EDIT:

Here are some previous posts related to "EAV Data"; note that this includes a pretty good article by "Joe Celko":

Table Design, EAV DATA:

   http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model
      Wikipedia Article

   http://weblogs.sqlteam.com/davidm/articles/12117.aspx
   http://codemagento.com/2011/03/joining-an-eav-table-to-flat-table/
   http://www.learn.geekinterview.com/it/data-modeling/entity-attribute-value-eav-/1.html

   http://sqlblog.com/blogs/aaron_bertrand/archive/2009/11/19/what-is-so-bad-about-eav-anyway.aspx
      Aaron Bertrand

   http://pratchev.blogspot.com/2010/07/refactoring-entity-attribute-value.html
      Plamen Ratchev

   http://social.msdn.microsoft.com/Forums/en-US/databasedesign/thread/087db982-2d9b-4d2a-903c-9e8fe5548f21
      Karen Lopez
      Aalam Rangi

Table design Problems -- EAV and OTLT design problems

   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/dc6f8f29-de93-427f-981b-858245f01a69/
      Arnie Rowland
      Alejandro Mesa
      Kent Waldrop

   http://www.simple-talk.com/sql/t-sql-programming/avoiding-the-eav-of-destruction/
      Article by Joe Celko

   http://weblogs.sqlteam.com/davidm/articles/12117.aspx
      David M

   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/29362835-0b49-4dd2-b5ae-33a60ebdc08e
      Kent Waldrop

   http://social.technet.microsoft.com/Forums/en-US/sqlgetstarted/thread/73b994ff-755b-40ae-9320-c7d21620e526
      Kent Waldrop
      George Neville
      David Stewart

   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/4b544f43-2d46-4ddd-8cb0-4647bf5ad303
      Kent Waldrop
      Naomi Nosonovsky

Answer 10

Hi Bart,

Thanks for your post.

If you have another question, please open a new thread in case of the thread could not be solved.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.