# Complex SQL Query

**URL:** <https://forum.inductiveautomation.com/t/complex-sql-query/4191>\
**Category:** Ignition\
**Created:** [March 22, 2012, 12:48pm UTC](https://forum.inductiveautomation.com/t/complex-sql-query/4191 "2012-03-22T12:48:57Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Curlyandshemp](https://avatars.discourse-cdn.com/v4/letter/c/bcef8e/32.png) [@Curlyandshemp](https://forum.inductiveautomation.com/u/Curlyandshemp)\
**Post date:** [March 22, 2012, 12:48pm UTC](https://forum.inductiveautomation.com/t/complex-sql-query/4191/1 "2012-03-22T12:48:57Z")

</div>

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.

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/1X/b435f09d42b8953452547bd137214cc7ec33405d.jpg)  
  
 ![](https://us1.discourse-cdn.com/inductiveautomation/original/1X/fb92fb903b31bf7eae2dd49eadb362c224391402.jpg)  
  
 ![](https://us1.discourse-cdn.com/inductiveautomation/original/1X/d3e7a0801f698271e65b6fa90ab826ca4bbc0fae.jpg)

---

<div class="post-metadata">

**Author:** ![tomt](https://avatars.discourse-cdn.com/v4/letter/t/90ced4/32.png) [@tomt](https://forum.inductiveautomation.com/u/tomt)\
**Post date:** [March 22, 2012, 2:58pm UTC](https://forum.inductiveautomation.com/t/complex-sql-query/4191/2 "2012-03-22T14:58:40Z")

</div>

If I understand right, you want a subquery to pull the other meta data for that tag. Try something like this:

```auto
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.

---

<div class="post-metadata">

**Author:** ![Curlyandshemp](https://avatars.discourse-cdn.com/v4/letter/c/bcef8e/32.png) [@Curlyandshemp](https://forum.inductiveautomation.com/u/Curlyandshemp)\
**Post date:** [March 22, 2012, 4:19pm UTC](https://forum.inductiveautomation.com/t/complex-sql-query/4191/3 "2012-03-22T16:19:43Z")

</div>

thanks alot! You are smart, I am dumb!

results below all in one table:

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/1X/641b1ddb2fed27ab9babe7ae1af4e756cf5e0ae2.jpg)  
  
 ![](https://us1.discourse-cdn.com/inductiveautomation/original/1X/478a0c124e3d5904f708d96d0c72adfc081ceb92.jpg)