I am using Google Charts to build an org chart. The way Google Charts works is that I need to set up rows that have both an ID and a parent node. Here is what I have so far:
SELECT e.employee as Name, e.manager as Manager, e.employee_title as Tooltip
FROM employees e
WHERE e.campaign='$campaign' AND e.email='$email' AND consultant=0 AND
(SELECT e.employee FROM employees WHERE e.manager = '' AND e.campaign='$campaign' AND e.email='$email' AND consultant=0)
GROUP BY Manager
I am getting an error from this. It says that my subquery returns more than one row. This is true since there are a few employees who have no manager field (means they don't report to anyone). The email and campaign is just because my system is multiuser and I need to separate employers in the table. You can ignore that.
I'd like to have separate query results for each top level manager. I was thinking of subqueries, first to get a top level manager (who doesn't report to anyone). But, there will be more than one of these "top level" guys in my table. Thus the error is correct. I'd settle for running separate queries, one for each top level manager in a loop. If this is doable?
Bottom line: I'd like to get separate result set for each top level manager.
Any suggestion on the best way to attack this problem? I am using mySQL.