CODEWITHSUNDEEP
sql-server
Paresh J
Paresh J

Reputation: 2419

Get values NOT IN Table1 when join with Table2

I have two tables as below:

Table1:

Col1    Col2
834       2
834       3
834       5
877       1
877       2
877       5
900      10
900       2 
900       3

Table2:

   Col3
    1
    10

Expected Output:

Col1    Col3
834      1
834      10
877      10
900      1

This is what I have done so far:

select distinct t1.Col1,A.Col3
from Table1
cross apply
(select * from Table2 left join Table1 on Col2  = Col3) A
order by t1.Col1,A.Col3

I have been trying lot using join, cross apply and other sql function to get me the expected output. Any help is appreciated.

Logic: I want to get the values of Col1 of Table1 for which Col3 does not matches with col2 of Table1. For Eg: Values 1 and 10 of Col3 of table2 is not there for value 834 of col1 of table1. Hence there are two rows for 834, one with value 1 and other with 10.

Upvotes: 0

Views: 67

Answers (3)

Jayesh Goyani
Jayesh Goyani

Reputation: 11154

Please try with the below code snippet.

DECLARE @Table1 TABLE( 
Col1     INT NOT NULL, 
Col2 INT NOT NULL 
); 

INSERT INTO @Table1 VALUES(834 ,      2)
INSERT INTO @Table1 VALUES(834 ,      3)
INSERT INTO @Table1 VALUES(834 ,      5)
INSERT INTO @Table1 VALUES(877  ,     1)
INSERT INTO @Table1 VALUES(877 ,      2)
INSERT INTO @Table1 VALUES(877 ,      5)
INSERT INTO @Table1 VALUES(900 ,     10)
INSERT INTO @Table1 VALUES(900  ,     2)
INSERT INTO @Table1 VALUES(900  ,     3)

DECLARE @Table2 TABLE( 
Col3     INT NOT NULL
); 

INSERT INTO @Table2 VALUES(1)
INSERT INTO @Table2 VALUES(10)

SELECT a.Col1,a.Col3 FROM (SELECT DISTINCT a.Col1,b.Col3 FROM @Table1 a,@Table2 b
WHERE a.Col2 <> b.Col3) a
LEFT JOIN @Table1 c on a.Col1 = c.Col1 and a.Col3 = c.Col2
WHERE c.Col1 is null

Upvotes: 1

eros harianto nugroho
eros harianto nugroho

Reputation: 62

select a.col1, a.col2  
from Table1 a  
where not exists(select * from Table2 b where a.col1 = b.col3)

This may work...

Upvotes: 0

Ash
Ash

Reputation: 2595

I doubt you need any join in here. All you want to select from table1 are the rows with matching value in table2. Something like this :

Select distinct col1, col2 from table1
Where col2 not in (select col3 from table2)

Upvotes: 0

Related Questions