komal145 (11/6/2012)

---

Hi friends

How to Exclude Records using join . I usually do not in

Example: I have a Table A and Table B

I want to exclude all the loans from table A where Table B has condition with COLUMN_B = "Y"


example In not in : select * from TableA where ColumnA not in ( Select * from TableB where ColumnA='Y')

Thanks,
Komal

SELECT *
FROM TableA a
INNER JOIN TableB b ON a.LoanID = b.LoanID
WHERE b.ColumnA <> 'Y'

CELKO (11/6/2012)

---

Where is your DDL? Why did you think you did not have to follow minimal Netiquette? You do not know that rows are not records!



SELECT * FROM A
EXCEPT
SELECT * FROM B WHERE B.col_b = 'Y':

I have the feeling that col_b such a stinking mess that it is a Boolean flag in SQL. Tell me that you are not that ignorant.

Sony Francis @EY (11/7/2012)

---

Try this

SELECT *
FROM TableA a
LEFT OUTER JOIN TableB b ON a.LoanID = b.LoanID
WHERE b.ColumnA <> 'Y'


When we use INNER JOIN it will consider only commom records. But the user wants to consider all records in first table

SELECT A.* 
FROM  TableA AS A 
WERE  NOT EXIST ( SELECT 1 
                           FROM TableB AS B
                           WHERE B.[KeyColumnToJoinOn] = A.[KeyColumnToJoinOn]
                               AND B.ColumnA='Y')

Sony Francis @EY (11/7/2012)

---

Try this

SELECT *
FROM TableA a
LEFT OUTER JOIN TableB b ON a.LoanID = b.LoanID
WHERE b.ColumnA <> 'Y'


When we use INNER JOIN it will consider only commom records. But the user wants to consider all records in first table

ChrisM@Work (11/7/2012)

---

If you reference an outer-joined table column in the WHERE clause, you turn that join into an INNER join - unless the reference is to a null value [WHERE b.ColumnA IS NULL].
If you want to filter an outer-joined table, put the filter into the join condition.