SQL Querry - Get Parent Name

[aljazjelen]

Hi everyone, hope you are doing well.

For some of you, the topic might be very simple and straightforward.

So I have made a querry which returns a list of all elements of type "Signal" in a selected package. Next step would be to obtain a name of a parent of an object "o" (similar to .getElementbyId() and then searching for a name in jscript).

Code: [Select]

select pkg.Name as "Package Name" , o.Name as "Name", o.Status as "Status", o.ParentID as "Parent ID", o.Object_ID as "Object ID", pkg.Package_ID as "Pkg Package_ID"
FROM t_object o, t_package pkg
where pkg.Package_ID IN (#Branch#) AND o.package_ID = pkg.Package_ID and o.Object_Type = "Signal"

However I am stuck at how to select a name of a parent, knowing the parent id?. Could anyone point me in the direction? Is this even possible with simply SQL querries in Scratchpad?

Thanks for your support!

Best regards

[Geert Bellekens]

Hi,

To get the parent name you need to join again with t_object on parentID = Object_ID

Code: [Select]

select pkg.Name as [Package Name] , o.Name as [Name], o.Status as [Status], o.ParentID as [Parent ID],
o.Object_ID as [Object ID], pkg.Package_ID as [Pkg Package_ID], parent.Name as [ParentName]
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 = 'Signal'
A few pointers
- Using the proper join syntax makes reading (and writing) queries a lot easier as the join condition is next to the joined table.
- I used a left join for parent as sometimes there is no parent object. Using an inner join would select only those that have a parent element.
- The parentheses are only there to please MS Access syntax. No need for them if you use a "real" database.
- Strings are indicated using single quotes in SQL. Double quotes are allowed by some vendors, but certainly not by all.
- If you have column names with spaces you better use the rectangle brackets instead of the double quotes. [Column Name] instead of "Column Name"

Geert

[aljazjelen]

Hi Geert,

This works like a charm. I will go through your hints and try to implement them in my SQLs in the future. The "join" feature was completely unknown to me until couple of minutes ago.
Thanks for the hints and support!

Happy New Year

Regards,
Aljaz