CODEWITHSUNDEEP
mysqlsqlsql-server
Eunito
Eunito

Reputation: 436

Tuple "not in" clause to run in mysql and mssql issue

I'm trying to get data from a table containing some where clauses using a query that runs both on MySQL and SQL Server. That query contains a tuple clause validation from the joined data not to exist in a subquery out of a different table. This query runs ok in MySQL but not in SQL Server. Tried "NOT EXISTS" without success...

SELECT
    a.col1,
    b.col2
FROM
    tableA a
    JOIN tableB b ON b.col_x = a.col_x
    WHERE a.col5 IN ('VAL1', 'VAL2', 'VAL3')
    AND (a.col2, b.col4) NOT IN (
        SELECT
            c.col2,
            c.col4
        from
            tableC c
    )

Running in SQL Server I get

An expression of non-boolean type specified in a context where a condition is expected, near ','.

In the "NOT IN / NOT EXISTS" part of the SQL and clause. Any ideas?

Upvotes: 0

Views: 543

Answers (1)

jarlh
jarlh

Reputation: 44776

NOT EXISTS can be used instead:

SELECT
    a.col1,
    b.col2
FROM
    tableA a
    JOIN tableB b ON b.col_x = a.col_x
WHERE a.col5 IN ('VAL1', 'VAL2', 'VAL3')
  AND NOT EXISTS    
  (
    SELECT *
    from tableC c
    WHERE c.col2 = a.col2 AND c.col4 = b.col4
  )

Upvotes: 6

Related Questions