Tagged values - SQL Query

[aljazjelen]

Hi everyone,

I have an sql querry below:

Code: [Select]

select pkg.Name as [Package Name] , parent.Name as [Parent Name] , o.Name as [Name], o.Status as [Status]
FROM ((t_object o
inner join t_package pkg on pkg.Package_ID = o.Package_ID)
left join t_object parent on parent.Object_ID = o.ParentID)
where o.Package_ID IN (#Branch#)
and o.Object_Type = 'Class'

which returns all classes, their status and parent name. Just as expected.

What if I would want to return also a value of specific tag?

So as I am really not so confident with SQL, I tried searching on the forums and found something like:

Code: [Select]

select t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE, t_object.Name, tag1.[Value], tag2.[Value]
from t_object, t_objectproperties tag1, t_objectproperties tag2
where t_object.Object_ID = tag1.Object_ID
and t_object.Object_ID = tag2.Object_ID
and tag1.[Property] = 'tag1'
and tag2.[Property] = 'tag2'

However I am having problems merging the 2 codes together (obvious problem, since I am not dealing with SQL on daily basis).

I tried something like:

Code: [Select]

select pkg.Name as [Package Name] , parent.Name as [Parent Name] , o.Name as [Name], o.Status as [Status], tag1.[Value]
FROM ((t_object o, t_objectproperties tag1
inner join t_package pkg on pkg.Package_ID = o.Package_ID)
left join t_object parent on parent.Object_ID = o.ParentID)
where o.Package_ID IN (#Branch#)
and o.Object_Type = 'Class'
and tag1.[Property] = 'Level'

But it yields a problem. I am not entirely sure if I understand the Inner join, left Join etc... so I assume I made a mistake over there.

Anyone could help me out?

Regards

[qwerty]

Geert can help for sure. But why now the join with #Branch#. Are you additionally wnating to search a branch? Your first query is only searching for a certain class element. So what exactly is your goal?

q.

[aljazjelen]

Hi Q, thanks for post!

So my goal is to select a package and search for all elements with a "type" and extract all required information, such as "Parent", "Status", "Tags" etc...

Its quite simple, but yet I have never done any SQL before, so I figured it would be faster if I asked experts.
I guess at some point, taking SQL course would not be a bad idea.

Regards

[Geert Bellekens]

You should probably choose to either use the JOIN syntax (best practice) or the "from table1, table2"  syntax.

Currently you seem to be mixing the two, which can't be a good idea.

The join between t_object (elements) and t_objectproperties (tagged values) is based on the shared column Object_ID, so

Code: [Select]

select * from t_object o
left join t_objectproperties tv on tv.Object_ID = o.Object_ID
will return a record for each tagged value it finds. You need the left join to include objects that don't have records in t_objectproperties.

Now if you want to select only a specific tagged value, say "tag1" you do

Code: [Select]

select o.name, tv.Value as tag1
from t_object o
left join t_objectproperties tv on tv.Object_ID = o.Object_ID
                                    and tv.Property = 'tag1'

Including another tagged value (tag2) can be done in the same way

Code: [Select]

select o.name, tv.Value as tag1, tv2.Value as tag2
from t_object o
left join t_objectproperties tv on tv.Object_ID = o.Object_ID
                                    and tv.Property = 'tag1'
left join t_objectproperties tv2 on tv2.Object_ID = o.Object_ID
                                    and tv2.Property = 'tag2'

When using .eap files the logic is the same, just add loads of parentheses (MS-Access syntax)

Geert

[aljazjelen]

Hi Geert!

Once again, thank you for support. With your help I figured out where the mistake was. Still I am not entirely sure why it has to be done like so. But thats on me to discover.

I thought that it is possible to use multiple "from" in junction with the JOIN syntax. Guess I was wrong. Thanks for letting me know!

Regards

[qwerty]

SQL isn't too complicated. Most annoying are the different dialects (not only when dealing with details). Also, you don't need to be an expert. With some basics you can do a lot.

q.

[Geert Bellekens]

You can probably combine the two syntax variants, but you shouldn't want to.
Why make things harder on yourself?

Geert