I wondering if what I am trying to accomplish can be done. I am trying to create a single table with data queried and joined from Ignition tables sqlt_as,sqlt_core and sqlt_meta. Queries and joins from sqlt_as and sqlt_core are not an issue ,but it is the table sqlt_meta I am struggling with. For each unique tagid in sqlt_meta, there are three records . I am trying to extract each record and based on the text in the name column, place in a unique column in the table dataset.
Only way I can figure out how to do is three separate tables each its own query.
Attached shots are of the three tables created, the sqlt_meta table with the 3 records per tagid and a typical query.
If I understand right, you want a subquery to pull the other meta data for that tag. Try something like this:
select
sm.tagid,
sc.name,
sc.path,
sm.stringval,
sa.statename,
sc.configchange,
(select sm2.stringval from sqlt_meta sm2
where sm.tagid = sm2.tagid and sm2.name = 'OPCServer') as OPCServer
from sqlt_meta sm
join sqlt_core sc on sc.id = sm.tagid
left join sqlt_as sa on sc.id = sa.tagid
where sm.name = 'OPCItemPath'Is uses the “current” tagid from the row you are “in” to drive the where clause of the subquery. Note the table alias is different, but the table is the same.
