CODEWITHSUNDEEP
sqlsql-server
Hemant Chaudhari
Hemant Chaudhari

Reputation: 23

Complex SQL join condition to Eliminate the NULL

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

How to get the single row output with not null values?

Expected Output

Upvotes: 2

Views: 56

Answers (2)

Arun
Arun

Reputation: 1106

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

Upvotes: 1

Mureinik
Mureinik

Reputation: 311018

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

Upvotes: 2

Related Questions