Complex SQL join condition to Eliminate the NULL-CodePudding

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

CodePudding user response：

MAX() will not include NULLs. Try this:

SELECT 
      e_id, 
      e_name, 
      MAX(gender) as gender, 
      MAX(age) as age, 
      MAX(contact) as contact
FROM employee
GROUP BY e_id, e_name

Note: If there are more than 1 Non-null values, tweak the code accordingly. Else you might end up in considering wrong one.

Look at the sample output in db<>fiddle

CodePudding user response：

Aggregate functions like max ignore nulls, so one possible trick is to group by the e_id and e_name, and apply max to the other columns:

SELECT   e_id, e_name, MAX(gender), MAX(contact), MAX(age)
FROM     employee
GROUP BY e_id, e_name