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.

CodePudding user response：

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:

db<>fiddle here