Author Topic: Tagged values - SQL Query  (Read 1259 times)

aljazjelen

  • EA User
  • **
  • Posts: 21
  • Karma: +0/-0
    • View Profile
Tagged values - SQL Query
« on: March 11, 2021, 12:24:58 am »
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

  • EA Guru
  • *****
  • Posts: 12893
  • Karma: +369/-296
  • I'm no guru at all
    • View Profile
Re: Tagged values - SQL Query
« Reply #1 on: March 11, 2021, 12:50:24 am »
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

  • EA User
  • **
  • Posts: 21
  • Karma: +0/-0
    • View Profile
Re: Tagged values - SQL Query
« Reply #2 on: March 11, 2021, 12:54:15 am »
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

  • EA Guru
  • *****
  • Posts: 11935
  • Karma: +464/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Tagged values - SQL Query
« Reply #3 on: March 11, 2021, 01:14:55 am »
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

  • EA User
  • **
  • Posts: 21
  • Karma: +0/-0
    • View Profile
Re: Tagged values - SQL Query
« Reply #4 on: March 11, 2021, 02:48:27 am »
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

  • EA Guru
  • *****
  • Posts: 12893
  • Karma: +369/-296
  • I'm no guru at all
    • View Profile
Re: Tagged values - SQL Query
« Reply #5 on: March 11, 2021, 02:59:41 am »
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

  • EA Guru
  • *****
  • Posts: 11935
  • Karma: +464/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Tagged values - SQL Query
« Reply #6 on: March 11, 2021, 05:07:01 am »
You can probably combine the two syntax variants, but you shouldn't want to.
Why make things harder on yourself?

Geert