Embedded List Query is reading String1 from all Parameter Tables

[Tanquen]

Each site is a template and has templates inside it and they all use the same Parameter Table “_Station Description” in the root of the template to hold the name of the site. A new template was added that also has a Parameter Table with a different name but these are now added to the Embedded List. Is there a way to have it ignore String1 from a certain Parameter Table? I tried adding the correct object name into the SELECT but I’m unsure how it should look. Something like CPARAMTABLE._Station Description.STRING1”

SELECT
CTEMPLATEINSTANCE.ID, CPARAMTABLE.STRING1 AS "~Station", CTEMPLATEINSTANCE.FULLNAME
FROM
CPARAMTABLE INNER JOIN CTEMPLATEINSTANCE ON CTEMPLATEINSTANCE.ID = CPARAMTABLE.PARENTGROUPID
WHERE
CTEMPLATEINSTANCE.FULLNAME LIKE 'North%'
ORDER BY
"~Station" ASC

I was able to add a Not Like to the template name but it seems like it would be better to have it only find the “_Station Description” Parameter Table.

WHERE
CTEMPLATEINSTANCE.FULLNAME LIKE 'North%' AND CTEMPLATEINSTANCE.FULLNAME NOT LIKE '%Tags%'

[BevanWeiss]

I'd recommend using aliases on your tables to add some self-documentation to the query.

Calling the table CTemplateInstance something else based on your use of it would generally be better, i.e. FROM CTemplateInstance as Site, and then you can call it 'Site' rather than having to type out CTemplateInstance.... all the time.

I'd normally try to start with the thing that is relatively static, i.e. the site/station/CTemplateInstance.  Do your initial filtering to get what you want from that.  And then you can LEFT OUTER JOIN in the things that you expect to exist, like the CParamTable entry.

SELECT Site.Id, SiteParamTable.String1 as "~Station", Site.FullName

FROM CTemplateInstance as "Site"

LEFT OUTER JOIN CParamTable as "SiteParamTable" ON "Site".Id="SiteParamTable".ParentGroupId

WHERE "Site".FullName LIKE 'North%'

AND "SiteParamTable".Name = 'Station Description'

ORDER BY "~Station" ASC

I'm going to assume that your parameter table name doesn't actually have an underscore (_) in it... because that's generally a bad idea.  Underscore is a special character in the SQL world, so is best avoided unless you want to be escaping strings all the time or getting really terrible performance and unwanted behaviour.