IGNITION SQL QUERY

Question

I am currently stuck with an SQL query in IGNITION DESIGNER.

I have a SQL table with some entries where the interesting part is built up as follows:

TableXY

ColumnA	ColumnB	ColumnC
Objekt 1	X	Order01
Objekt 1	X	Order01
Objekt 1	X	Order02
Objekt 1	Y	Order02
Objekt 2	X	Order03
Objekt 2	X	Order03
Objekt 2	X	Order04
Objekt 2	Y	Order04

I am searching within my table for all ColumnC that belong to "Object 1" and do not yet have a row with a "Y" in ColumnB.

As an output should thus appear:

ColumnC
Order01

My approach is as follows...

SELECT ColumnC FROM TableXY  WHERE ColumnA='Object 1' AND ColumnB = 'X'  IN ( SELECT FROM TableXY  WHERE ColumnA='Object 1' AND NOT ColumnB = 'Y' ) 

Do you have any idea what could be wrong?

Or how I could do it better?

Answer 1

This query produces the expected result in my test database:

SELECT DISTINCT ColumnC 
FROM testdb.tablexy
WHERE ColumnA = 'Objekt 1'
    AND ColumnC NOT IN 
    (
        SELECT DISTINCT `ColumnC`
        FROM testdb.tablexy
        WHERE ColumnA = 'Objekt 1'
            AND ColumnB = 'Y'
    );

It subqueries the unwanted result to make sure that none of the returned results are included in the subquery.

Result: