sql set operation and where clause

Question

Hi all

Is it possible to have a query like this (I'm getting error) :

SELECT ColumnA FROM Table1
EXCEPT
SELECT ColumnB FROM Table2
WHERE Table1.ColumnC = Table2.ColumnC

SQL can't bind the Table1.colmnC in my where clause . is there any way to run this query ? or another way to get the same result ? I know that I can do this using temp tables or ... but I want to do it with a set operation .

thanks

Answer 1

first glance:

SELECT ColumnA FROM Table1
WHERE
    NOT EXISTS (SELECT * FROM Table2 WHERE Table1.ColumnC = Table2.ColumnC)

However, I think it's this you want to exclude column B values from the columnA values, where columnC matches columnsA and B:

SELECT ColumnA FROM Table1
WHERE
   NOT EXISTS (
      SELECT * FROM Table2
      WHERE Table1.ColumnC = Table2.ColumnC AND Table1.ColumnA = Table2.ColumnB)

Answer 2

What you are doing is

(SELECT ColumnA FROM Table1)
EXECPT 
(SELECT ColumnB FROM Table2
 WHERE Table1.ColumnC = Table2.ColumnC)

The brackets show why it doesn't work.

I'm slightly confused as to what you are trying to achieve.

Is it everything in ColumnA from Table1 where ColumnA isn't in Table2.ColumnB and Table1.ColumnC is equal to Table2.ColumnC ?

SELECT ColumnA FROM Table
WHERE EXISTS (SELECT * FROM Table2 
              WHERE Table1.ColumnC = Table2.ColumnC
              AND Table1.ColumnA <> Table2.ColumnB)

Answer 3

To just fix the error for your query, following should run fine if data types of ColumnA and ColumnB are same.

SELECT ColumnA FROM Table1
EXCEPT
SELECT ColumnB FROM Table2

Except compares the resultsets. There is no concept of joining the resultsets over some column.