Fetch Parent Record anyway even if child condition doesn't meet without using ON

Question

I have two tables Employee and Address. One Employee can have multiple Address. Here I want to fetch 'active employee details' and 'active address details' of a particular emp_id. I can achieve this by below query :

Table Structure:
Employee(emp_id,name,is_active)
Address(add_id,emp_id,address,is_active)

Query:

SELECT * FROM EMPLOYEE e
LEFT OUTER JOIN ADDRESS a
ON e.emp_id=a.emp_id 
WHERE e.is_active='A'
AND a.is_active='A';

Using above query it does not return any employee details if no active address. I want to return active employee details anyways even if it does not have any active address.

Note: as I am using Hibernate looking for a query without using ON . Only Where clause can be used here.

Kindly suggest.

Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Feb 15 '22 at 02:24

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2022-02-14T17:48:23.960

2

You need to put a.is_active='A' in ON clause not in WHERE clause

SELECT * FROM EMPLOYEE e
LEFT OUTER JOIN ADDRESS a
ON e.emp_id=a.emp_id AND a.is_active='A'
WHERE e.is_active='A';

Since you have restrictions on using condition in on clause you can try below approach. It will return rows where address is active or address is not available (assuming that is_active column is never null in address table).

Schema and insert statements:

 create table EMPLOYEES(emp_id int, name varchar(20), is_active varchar(10));
 create table Address(add_id int ,emp_id int ,address varchar(50),is_active varchar(10));

 insert into EMPLOYEES values (1,'NAME1','A');
 insert into Address values(1,1,'addr1','N');
 insert into Address values(2,1,'addr1','N');

Query:

 SELECT * FROM EMPLOYEES e
 LEFT OUTER JOIN (select * from Address where is_active='A') a
 ON e.emp_id=a.emp_id 
 WHERE e.is_active='A'
 AND (a.is_active='A' or a.is_active is null);

Output:

EMP_ID	NAME	IS_ACTIVE	ADD_ID	EMP_ID	ADDRESS	IS_ACTIVE
1	NAME1	A	null	null	null	null

db<>fiddle here

edited Feb 14 '22 at 17:48

answered Feb 13 '22 at 09:11

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

I can not use ON due to some hibernate constraints. Looking for a solution using where clause or anything else. – Avinash Jethy Feb 13 '22 at 17:58
What if there one employee has 2 address and all those 2 address are inactive. In this case even if employee is active will not fetch the employee. Not sure how to fix it. Can you give any idea – Avinash Jethy Feb 14 '22 at 16:50
@AvinashJethy I have revised my answer. – Kazi Mohammad Ali Nur Romel Feb 14 '22 at 17:48
In case of emp_id 101 has multiple add_id 201,202 etc. Then it's fetching the records in 2 separate row. Is it possible to return all address of an employee in a single row . – Avinash Jethy Feb 16 '22 at 04:45

Fetch Parent Record anyway even if child condition doesn't meet without using ON

1 Answers1