CODEWITHSUNDEEP
sqlsql-serverleft-join
Registered User
Registered User

Reputation: 1564

Wrong behaviour with left outer join?

SQL Fiddle : http://sqlfiddle.com/#!2/49db7/2 

create table EmpDetails
(
emp_code int,
e_type     varchar(10)
);

insert into EmpDetails values (100,'A');
insert into EmpDetails values (101,'D');
insert into EmpDetails values (102,'A');
insert into EmpDetails values (103,'D');

create table QDetails
(
id  int,
emp_code int,
dn_num int
);

insert into QDetails values (1,100,NULL);
insert into QDetails values (2,101,4343);
insert into QDetails values (3,101,4343);
insert into QDetails values (4,103,NULL);
insert into QDetails values (5,103,NULL);
insert into QDetails values (6,100,NULL);

select * from EmpDetails
select * from QDetails

-- expected result 

1   100 NULL
6   100 NULL
2   101 4343
3   101 4343

--When e_type = A it should include rows from QDetails doesn't matter dn_num is null or not null --but when e_type = D then from QDetails it should include only NOT NULL values should ignore null

select e.emp_code, e.e_type, q.dn_num from empdetails e left join qdetails q
on e.emp_code = q.emp_code and (e.e_type = 'D' and q.dn_num is not null)

--Above query I tried includes 103 D NULL which I don't need and exclueds 6 100 NULL which i need.

Upvotes: 0

Views: 77

Answers (5)

Uddesh
Uddesh

Reputation: 1

This is the correct query.

        select q.id,e.emp_code,q.dn_num 
        from empdetails e join qdetails q
        on e.emp_code = q.emp_code
        where (e.e_type = 'D' and q.dn_num is not null)  or  e.e_type = 'A'  order by e.e_type;

Upvotes: 0

kristof
kristof

Reputation: 53824

I am not sure why you are using left join here.
You can get the results you specified with inner join

select
  e.emp_code
  ,e.e_type
  ,q.dn_num 
from 
  empdetails e 
  inner join qdetails q on e.emp_code = q.emp_code
where 
  e.e_type = 'A'
  or (e.e_type = 'D' and q.dn_num is not null)
order by
  e.emp_code
  ,e.e_type

The left join would be used if you also wanted to list records from empdetails table that have no match in qdetails

Upvotes: 1

Paddy
Paddy

Reputation: 33857

Your problem is your q.dn_num is not null condition, it is specifically excluding those records that you state that you want. Removing that should fix it.

select e.emp_code, e.e_type, q.dn_num 
from empdetails e 
left join qdetails q
   on e.emp_code = q.emp_code 
WHERE (e.e_type = 'D' and q.db_num is not null)
OR e.e_type = 'A'

Additionally, it is a general convention to use your join to specify only the join criteria and your where clause to filter (there are reasons why you may not want to do this, but depends on your query).

Writing your queries as above may make them easier to read later on.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269723

You need to implement the logic correctly. Your conditions mention 'A', but that is not in the conditions.

Then, you don't need a left join. You don't want emp_code = 102. This has no matches, so presumably it drops out.

The following seems to implement the logic:

select e.emp_code, e.e_type, q.dn_num , q.id
from empdetails e join
     qdetails q
     on e.emp_code = q.emp_code
where (e.e_type = 'D' and q.dn_num is not null) or
      e.e_type = 'A';

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

SELECT     e.emp_code, 
           e.e_type,  
           q.dn_num 
FROM       empdetails e 
           LEFT JOIN  qdetails q
               ON e.emp_code = q.emp_code 
WHERE      (
               e.e_type = 'A' OR 
              (
                 e.e_type = 'D' AND
                  q.dn_num is not null
              )
           ) AND q.id is not null

SQL Fiddle Demo

Upvotes: 0

Related Questions