NOT IN Problem

  • Hi! I need a query like this, with one restriction: I can't have a condition in the subquery. I have tried LEFT OUTER JOIN ON ColumnB <> ColumnC but it behaves like an INNER JOIN

    Select ColumnA, ColumnB from TableA where ColumnB NOT IN (Select ColumnC from TableB where ColumnD = "Value1")

    where ColumnA = "Value2")

    Thanks for your help

  • Have you tried:

    Select ColumnA, ColumnB from TableA where ColumnB NOT IN (Select ColumnC from TableB where ColumnD = "Value1")

    and ColumnA = "Value2"

  • Yes, I have tried many different options like this, but I have the restriction of not having any "Value" inside the subquery. Describing the problem:

    TableA

    01 Part1

    02 Part2

    03 Part3

    04 Part4

    05 Part5

    TableB

    500 01

    500 02

    TableA is a foreign key for TableB and I need a query that shows Parts from TableA that I don't have on TableB without using any "Value" inside the subquery. I thought right joins might work but they don't do so.

    Thanks

  • This will work well if TableA is not extremely large. Also consider that NOT IN does not work "as might be expected" if there is a NULL value in the IN list.

    INSERT#TableA

    SELECT*

    FROMTableA

    DELETE#TableA

    FROM#TableA

    JOINTableB

    ONColumnB = ColumnC

    SELECT*

    FROM#TempA

    Edited by - mromm on 03/11/2003 1:11:51 PM

    Edited by - mromm on 03/11/2003 1:56:43 PM

  • I haven't tested this, but...

    select a.colB

    from tableA a

    left join tableB b

    on a.colA = b.colB

    where b.colB IS NULL

    If I got it right, this should retrieve the parts (colb) from tableA when there isn't a match in tableB.

    -SQLBill

  • I tested it. Works perfectly. Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply