Complex SQL join condition to Eliminate the NULL

Posted on December 1, 2021 in General

CREATE TABLE EMP(E_ID INT,E_NAME VARCHAR(20),GENDER VARCHAR(20),CONTACT INT,AGE INT);

INSERT INTO EMP VALUES(1001,'ABC','MALE',NULL,NULL),(1001,'ABC',NULL,8989,NULL),(1001,'ABC',NULL,NULL,28);

I tried following query.. But not works..

SELECT A.E_ID, B.E_NAME, B.GENDER, B.CONTACT, B.AGE FROM EMP AS A INNER JOIN ENT AS B ON A.E_ID = B.E_ID AND A.E_NAME = B.E_NAME AND A.GENDER = B.GENDER AND A.CONTACT = A.CONTACT where A.CONTACT IS NOT NULL;

Employee Table

1: https://i.stack.imgur.com/0H8EW.png**How to get the single row output with not null values?**

Expected Output