Need Help with a mysql query

Question

I have 2 table first table. employee( empid- primary key ,empname) eg: employee table

1 john 

2 martyn 

3 davis 

second table documents(empid,documentname) eg :

1 address.doc 

1 worksheet.doc 

1 salaryslip.doc 

3 address.doc 

each employee have only one entry in employee table but in document table , zero or more entries are inserted for each employee.

i want to write a query that gives an output shown in below

empid empname documentflag 
1 john true 
2 martyn null 
3 davis true 

that is, if documents table have at least one entry for an employee ,then set corresponding document flag is true. otherwise set document value as null. is there any efficient way using joins. Thanks in advance

Answer 1

You can do this with a LEFT JOIN to include the documents table, a GROUP BY so that you only get 1 employee per row, and an IF() construct to output the values you want for documentflag.

SELECT e.empid, e.empname, IF(d.empid IS NULL, NULL, TRUE) `documentflag`
FROM employee e
LEFT JOIN documents d ON (d.empid = e.empid)
GROUP BY e.empid

Answer 2

SELECT
    empid, 
    empname,
    documentFlag
FROM employee emp 
LEFT OUTER JOIN 
(SELECT doc.empid, count(doc.empid)as documentFlag
 FROM documents doc Group by empid) X
ON emp.empid = X.empid