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

You could try a combination of UPDATE and IF NOT NULL ...

Matt Bannert
– Matt Bannert

2010-09-29 10:08:50 +00:00
Commented Sep 29, 2010 at 10:08 — Matt Bannert
– Matt Bannert, Commented Sep 29, 2010 at 10:08

Gus · Accepted Answer · 2010-09-29 10:10:31Z

2

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

answered Sep 29, 2010 at 10:10

Gus

7,3792 gold badges29 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jagmag · Accepted Answer · 2010-09-29 10:25:38Z

0

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

edited Sep 29, 2010 at 10:25

answered Sep 29, 2010 at 10:18

Jagmag

10.4k1 gold badge37 silver badges60 bronze badges

Collectives™ on Stack Overflow

Need Help with a mysql query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related