CODEWITHSUNDEEP
sqlsql-server-2008
TD2013
TD2013

Reputation: 97

SQL left join issue

Table1

Id           value
1               1
1               4

Table2

id      Detailid          value
 1       1                 1
 1       2                 2
 1       3                 3
 1       4                 4
 1       5                 5
 1       6                 6

I want results 

Id      Detaild      value
1        1           1
1        2          null
1        3          null 
1        4          4
1        5          null
1        6          null

My query below gives me 2 extra rows with null 

select distinct t1.id,t2.detailid  
 ,case when t1.value IN(t2.Value) then t1.value else null end as value  
from table1 t1  
left outer join table2 t2  
on t1.id= t2.id

I am getting 

Id       Detaild    value  
1        1          null ----dont need  
1        1          1  
1        2          null  
1        3          null   
1        4          null ---dont need  
1        4          4  
1        5          null  
1        6          null

Upvotes: 0

Views: 176

Answers (5)

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

From what you have given as data, this query will do the trick :

SELECT isnull(t1.Id,1), t2.Detailid, t1.value 
FROM Table2 AS t2
LEFT OUTER JOIN Table1 AS t1 ON t1.Detailid = t2.Detailid AND t1.ID = 1

SQLFIDDLE : http://www.sqlfiddle.com/#!3/4c808/8/0

EDIT :

So take a look at the following query, based on you last edit :

SELECT t2.Id, t2.Detailid, t1.value 
FROM Table2 AS t2
LEFT OUTER JOIN Table1 AS t1 ON t1.Id = t2.Id AND t1.value = t2.value
WHERE t2.Id = 1

SQLFIDDLE : http://www.sqlfiddle.com/#!3/94f21a/5/0

RE-EDIT : 

SELECT t2.Id, t2.Detailid, t1.value 
FROM Table2 AS t2
LEFT OUTER JOIN Table1 AS t1 ON t1.Id = t2.Id AND t1.value = t2.value
WHERE t2.id IN (SELECT Id FROM Table1)

SQLFIDDLE : http://www.sqlfiddle.com/#!3/beede/7/0

If (SELECT Id FROM Table1) returns too many rows, try something like this instead : 

 SELECT DISTINCT t2.Id, t2.Detailid, t1.value 
 FROM Table2 AS t2
 INNER JOIN Table1 AS t ON t.Id = t2.Id
 LEFT OUTER JOIN Table1 AS t1 ON t1.Id = t2.Id AND t1.value = t2.value

SQLFIDDLE : http://www.sqlfiddle.com/#!3/beede/9/0

Upvotes: 0

Praveen Prasannan
Praveen Prasannan

Reputation: 7133

select t2.[Id],t2.Detailid,t1.value
from Table2 t2
left join Table1 t1
on t2.value = t1.value;

FIDDLE

Upvotes: 0

Code Lღver
Code Lღver

Reputation: 15593

SELECT t1.Id, t1.DetailId, t1.value 
FROM table1 AS t1 
LEFT JOIN table2 AS t2. ON t1.Detaild = t2.detailid;

Use the above query.

Upvotes: 0

sweetu514
sweetu514

Reputation: 65

SELECT t1.Id, t2.DetailId, t1.value 
FROM table1 AS t1 
LEFT OUTER JOIN table2 AS t2. ON t1.DetailId = t2.detailId 
WHERE t2.DetailId NOT IN (5,6);

Upvotes: 1

Mentatmatt
Mentatmatt

Reputation: 525

That's because you don't have a row in Table1 with a foreign key to DetailId 2 and 3, so how else can it return those in the join?

Upvotes: 1

Related Questions