InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 08:22:06 GMT20http://www.sqlservercentral.com/Forums/Topic1381749-392-1.aspxHi here is the example:DROP Table #aDROp Table #bCreate table #a(Col1 int,COL2 varchar(10))Create table #b(col1 int,COL2 varchar(10))insert into #aValues (1,'Y')insert into #aValues (2,'N')insert into #aValues (3,'Y')insert into #aValues (4,'N')insert into #bValues (1,'Y')insert into #bValues (2,'N')Select * from #a a where a.COL2 not in ( select Col2 from #b B where b.COL2='Y')Hope this helps!!! because I wanted to use join , not except .Wed, 07 Nov 2012 07:53:34 GMTkomal145http://www.sqlservercentral.com/Forums/Topic1381749-392-1.aspx[quote][b]ChrisM@Work (11/7/2012)[/b][hr][quote][b]Sony Francis @EY (11/7/2012)[/b][hr]Try thisSELECT *FROM TableA aLEFT OUTER JOIN TableB b ON a.LoanID = b.LoanIDWHERE 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[/quote]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.[/quote]These are the reasons I always do better with DDL and sample data! :hehe:Wed, 07 Nov 2012 07:14:40 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1381749-392-1.aspx[quote][b]Sony Francis @EY (11/7/2012)[/b][hr]Try thisSELECT *FROM TableA aLEFT OUTER JOIN TableB b ON a.LoanID = b.LoanIDWHERE 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[/quote]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.Wed, 07 Nov 2012 07:00:50 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1381749-392-1.aspx[quote][b]Sony Francis @EY (11/7/2012)[/b][hr]Try thisSELECT *FROM TableA aLEFT OUTER JOIN TableB b ON a.LoanID = b.LoanIDWHERE 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[/quote]If you don't need anything in output from TableB, then using NOT EXISTS will make more sense and most likely give better performance too.[code="sql"]SELECT A.* FROM TableA AS A WERE NOT EXIST ( SELECT 1 FROM TableB AS B WHERE B.[KeyColumnToJoinOn] = A.[KeyColumnToJoinOn] AND B.ColumnA='Y')[/code]Wed, 07 Nov 2012 04:44:28 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1381749-392-1.aspxTry thisSELECT *FROM TableA aLEFT OUTER JOIN TableB b ON a.LoanID = b.LoanIDWHERE b.ColumnA <> 'Y'When we use INNER JOIN it will consider only commom records. But the user wants to consider all records in first tableWed, 07 Nov 2012 02:41:05 GMTSony Francis @EYhttp://www.sqlservercentral.com/Forums/Topic1381749-392-1.aspx[quote][b]CELKO (11/6/2012)[/b][hr]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! [quote] I want to exclude all the loans from table A where Table B has condition with COLUMN_B = "Y" [/quote]SELECT * FROM AEXCEPTSELECT * 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.[/quote]Ouch! Dude that was pretty harsh!Regardless that is an alternative solution but only if both tables have exactly the same columns and as long as that never changes.Use of * on a SELECT statement is not recommended in any Prod environment.Tue, 06 Nov 2012 21:08:13 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1381749-392-1.aspxWhere is your DDL? Why did you think you did not have to follow minimal Netiquette? You do not know that rows are not records! [quote ] I want to exclude all the loans from table A where Table B has condition with COLUMN_B = "Y" [/quote]SELECT * FROM AEXCEPTSELECT * 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.Tue, 06 Nov 2012 20:15:35 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1381749-392-1.aspx[quote][b]komal145 (11/6/2012)[/b][hr]Hi friends How to Exclude Records using join . I usually do not in Example: I have a Table A and Table BI 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[/quote]Probably something like this:[code="sql"]SELECT *FROM TableA aINNER JOIN TableB b ON a.LoanID = b.LoanIDWHERE b.ColumnA <> 'Y'[/code]Tue, 06 Nov 2012 18:41:33 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1381749-392-1.aspxHi friends How to Exclude Records using join . I usually do not in Example: I have a Table A and Table BI 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,KomalTue, 06 Nov 2012 16:51:51 GMTkomal145