Help - SQL Except Statement - Need an example of How to

General Discussion
1

I want to compare and SQL table to an Ignition Table/Dataset and only list the differences in a new table. I’m trying to bind The SQL statement below to a table, which is intended to only display data that is different. *Note: I’ve attached an image of the two tables in this post for a visual.

SELECT groups.g_name FROM groups
EXCEPT
SELECT g_name FROM {Root Container.TableMemberOf.data}
WHERE {Root Container.TableMemberOf.data.g_name} = groups.g_name

I’ve also tried:
SELECT groups.g_name FROM groups
WHERE NOT EXISTS
(SELECT * FROM {Root Container.TableMemberOf.data}
WHERE {Root Container.TableMemberOf.data} = groups.g_name)

Note: There is only one column on my igntion table that displays or lists the group names. there is no id on the ignition table. Therefore in the script above, I’m using the “groups.group” name from the SQL table and the filter of “*” on the ignition table, becaust the igntion table has no id.

Untitled.png1187×704 66.5 KB

2

You cannot do an SQL query against the table dataset as of Ignition 7.5.6
However, you could create another table in your database for the items you want to exclude…

Then you should be able to use either example you provided.

Cheers,
Chris

3

Copy. I’ll go that route. Thanks for the feedback.