March 11, 2003 at 10:59 am
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
March 11, 2003 at 12:07 pm
Have you tried:
Select ColumnA, ColumnB from TableA where ColumnB NOT IN (Select ColumnC from TableB where ColumnD = "Value1")
and ColumnA = "Value2"
March 11, 2003 at 12:52 pm
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
March 11, 2003 at 1:11 pm
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
March 12, 2003 at 8:21 am
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
March 31, 2003 at 9:24 am
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