Querying multiple optional tag values in SQLServer

[Mauricio Moya (Arquesoft)]

We are implementing a custom search in SQL (for a model stored in SQLServer) trying to get a list of elements and their optional 'n' tagged values.

In our scenary, there are some elements without tagged values, other with some (< 'n') tagged values and other with all the 'n' tagged values.

We are trying a query structured like that:

Code: [Select]

SELECT o.ea_guid AS CLASSGUID,
       o.Object_Type AS CLASSTYPE,
       o.Name,
       tv1.Value AS tv1,
       tv2.Value AS tv1,
       tv3.Value AS tv1,
       ...
       tvn.Value AS tvn
FROM t_object o
     LEFT JOIN t_objectproperties tv1 ON tv1.Object_ID = o.Object_ID
     LEFT JOIN t_objectproperties tv2 ON tv2.Object_ID = o.Object_ID
     LEFT JOIN t_objectproperties tv3 ON tv3.Object_ID = o.Object_ID
     ...
     LEFT JOIN t_objectproperties tvn ON tvn.Object_ID = o.Object_ID
WHERE tv1.Property = 'TVName1'
  AND tv2.Property = 'TVName2'
  AND tv3.Property = 'TVName3'
  ...
  AND tvn.Property = 'TVNameN'
But it does not returns the expected results (all the elements and the values of the tags if they are present).

Any idea in how to structure this query? What's wrong?

[hd]

Aliases are wrong?

tv1.Value AS tv1,
tv2.Value AS tv1,
tv3.Value AS tv1,
       ...

should be...
tv1.Value AS tv1,
tv2.Value AS tv2,
tv3.Value AS tv3,
       ...

[Nizam]

Not sure whats returned, but this may also be an option,

Code: [Select]

SELECT t_object.ea_guid AS CLASSGUID,
       t_object.Object_Type AS CLASSTYPE,
       t_object.Name,
   t_object.Stereotype,
   t_object.Author,
       t_objectproperties.Value As TAGVALUE
FROM t_object, t_objectproperties
WHERE t_object.Object_ID = t_objectProperties.Object_ID and t_objectproperties.Property in ('TV Name1', 'TV Name2')


[Mauricio Moya (Arquesoft)]

Aliases are wrong?

tv1.Value AS tv1,
tv2.Value AS tv1,
tv3.Value AS tv1,
       ...

should be...
tv1.Value AS tv1,
tv2.Value AS tv2,
tv3.Value AS tv3,
       ...

Sure, just a typo when copy-pasting and editing.

[Mauricio Moya (Arquesoft)]

Not sure whats returned, but this may also be an option,

Code: [Select]

SELECT t_object.ea_guid AS CLASSGUID,
       t_object.Object_Type AS CLASSTYPE,
       t_object.Name,
   t_object.Stereotype,
   t_object.Author,
       t_objectproperties.Value As TAGVALUE
FROM t_object, t_objectproperties
WHERE t_object.Object_ID = t_objectProperties.Object_ID and t_objectproperties.Property in ('TV Name1', 'TV Name2')


The problem with this option is that there is only one column (in the results) with the value of all tag values and for each element you have multiple rows (one for each tag value).

The idea is to have a single row per element and the tag values as columns, even with bull values if they don't exist.

[Geert Bellekens]

The problem is that your where clause makes the left joins inner joins again by checking on the tv.Property.
So whenever a tagged is existent, the whole record won't show.

If you include that statement in "ON" statement you won't have that problem.

For SQL server you can do something like this:

Code: [Select]

SELECT o.ea_guid AS CLASSGUID,
       o.Object_Type AS CLASSTYPE,
       o.Name,
       tv1.Value AS tv1,
       tv2.Value AS tv1,
       tv3.Value AS tv1,
       ...
       tvn.Value AS tvn
FROM t_object o
     LEFT JOIN t_objectproperties tv1 ON tv1.Object_ID = o.Object_ID
                                                    AND tv1.Property = 'TVName1'
     LEFT JOIN t_objectproperties tv2 ON tv2.Object_ID = o.Object_ID
                                                    AND tv2.Property = 'TVName2'
     LEFT JOIN t_objectproperties tv3 ON tv3.Object_ID = o.Object_ID
                                                    AND tv3.Property = 'TVName3'
     ..
     LEFT JOIN t_objectproperties tvn ON tvn.Object_ID = o.Object_ID
                                                        AND tvn.Property = 'TVNameN'
For MS Access syntax you'll have to add parenthesis

Code: [Select]

SELECT o.ea_guid AS CLASSGUID,
       o.Object_Type AS CLASSTYPE,
       o.Name,
       tv1.Value AS tv1,
       tv2.Value AS tv1,
       tv3.Value AS tv1,
       ...
       tvn.Value AS tvn
FROM ((((t_object o
     LEFT JOIN t_objectproperties tv1 ON (tv1.Object_ID = o.Object_ID
                                                    AND tv1.Property = 'TVName1'))
     LEFT JOIN t_objectproperties tv2 ON (tv2.Object_ID = o.Object_ID
                                                    AND tv2.Property = 'TVName2'))
     LEFT JOIN t_objectproperties tv3 ON (tv3.Object_ID = o.Object_ID
                                                    AND tv3.Property = 'TVName3'))
     ..
     LEFT JOIN t_objectproperties tvn ON (tvn.Object_ID = o.Object_ID
                                                        AND tvn.Property = 'TVNameN'))
Geert

[Mauricio Moya (Arquesoft)]

Thanks Geert, it worked!