SQL Returning a null value

Question

Just running a SQL and wrote a query with a sub query but it has returned a null value, when its not suppose too. Not to sure if anyone can help without the database but here is my query:

*This is the exercise question: 'Use an SQL statement with a sub-query to list the employee ID and full name of those employees who are managers. Use the column headings Manager ID and Manager.'

SELECT 
    ReportsTo As 'Manager ID',
    CONCAT(FirstName,' ',Lastname) As 'Manager'

FROM 
    Employees
WHERE 
    EmployeeID IN 
        (SELECT  ReportsTo 
            FROM Employees);

If any of the argument of CONCAT() is null then full result will be null. You can user CONCAT_WS(' ', FirstName, Lastname) instead. Sample data and desired result will be more helpful — Ambrish, May 26 '14 at 04:48
List the employees who are managers and display there full name and employee Id. — user3444694, May 26 '14 at 04:49
You probably need to replace `ReportsTo` in your main query to `EmployeeID`. Does your subquery return anything? — rikitikitik, May 26 '14 at 04:52
It returns all the correct results besides one value. returns the correct full names and correct employeeID except for one name. it returns its full name but not the ID — user3444694, May 26 '14 at 04:58

sgeddes · Accepted Answer · 2014-05-26T05:03:30.023

Assuming I'm understanding your question, you want to return those employees that are marked as managers (denoted by the reportto column). If so, you could use EXISTS:

select employeeid, concat(firstname,' ', lastname) fullname
from employees e
where exists (
  select 1
  from employees e2
  where e.employeeid = e2.reportsto)

SQL Fiddle Demo

This will return a list of employees with their id and full name whose id exists in the reportsto field.

If you prefer the in syntax, then this should work the same:

select employeeid, concat(firstname,' ', lastname) fullname
from employees e
where employeeid in (
  select reportsto
  from employees
)

Thanks this was what I was look for. returned the results that I needed. — user3444694, May 26 '14 at 05:08

score 0 · Answer 2 · edited May 26 '14 at 05:53

First, the subquery appears incorrect. You are trying to say "Grab me ReportsTo and peoples names from the Employees table where the Employee ID is equal to any of the ReportsTo fields in the same table"

You would need to change the ReportsTo in the subquery to EmployeeID. IE

SELECT 
    ReportsTo As 'Manager ID',
    CONCAT(FirstName,' ',Lastname) As 'Manager'

FROM 
    Employees
WHERE 
    EmployeeID IN 
    (SELECT  EmployeeID --Here is where i've changed the value 
        FROM Employees);

However this query in itself is no different from just simply writing:

SELECT 
    ReportsTo As 'Manager ID',
    CONCAT(FirstName,' ',Lastname) As 'Manager'

FROM 
    Employees

If you are trying to filter out your results somehow, what "Employees" records do you want to filter out? PLease give more info.

It was a exercise were I had to use a sub query. – user3444694 May 26 '14 at 05:03 — user3444694, May 26 '14 at 05:03

score 0 · Answer 3 · edited May 23 '17 at 12:27

User CONCATWS function istead of CONCAT which handle null value.

refer link MySQL CONCAT returns NULL if any field contain NULL

this below is sqlserver example. In sqlserver ISNULL function to handle null value

declare @Employees table(EmployeeID int, firstname varchar(50), lastname varchar(50), ReportsTo int null)

insert into @Employees values ( 1,'abc','xyz',0),( 2,'def','xyz1',1),( 3,'abc1','xyz2',null)

SELECT 
    ReportsTo As 'Manager ID',
    isnull( FirstName ,'') + '' + ISNULL(lastname,0 ) as 'Manager'
    --CONCAT(FirstName,' ',Lastname) As 'Manager'

FROM 
    @Employees
WHERE 
    EmployeeID IN 
        (SELECT  ReportsTo 
            FROM @Employees);

SQL Returning a null value

3 Answers3