MySQL Gurus: How to pull a complex grid of data from MySQL database with one query?
- by iopener
Hopefully this is less complex than I think.
I have one table of companies, and another table of jobs, and a third table with that contains a single entry for each employee in each job from each company. NOTE: Some companies won't have employees in some jobs, and some companies will have more than one employee in some jobs.
The company table has a companyid and companyname field, the job table has a jobid and jobtitle field, and the employee table has employeeid, companyid, jobid and employeename fields.
I want to build a table like this:
+-----------+-----------+-----------+
| Company A | Company B | Company C |
------+-----------+-----------+-----------+
Job A | Emp 1 | Emp 2 | |
------+-----------+-----------+-----------+
Job B | Emp 3 | | Emp 4 |
| | | Emp 5 |
------+-----------+-----------+-----------+
Job C | | Emp 6 | |
| | Emp 7 | |
| | Emp 8 | |
------+-----------+-----------+-----------+
I had previously been looping through a result set of jobs, and for each job, looping through a result set of each company, and for each company, looping through each employee and printing it in a table (gross, but performance was not supposed to be a consideration). The app has grown in popularity, and now we have 100 companies and hundreds of jobs, and the server is crapping out (all the id fields are indexed).
Any suggestions on how to write a single query to get this data? I don't need the company names or job titles (obviously), but I do need some way to identify where each row from the result should be printed. I'm imagining a result set that just contained a long list of joined employees, and I could write a loop to use the companyid and employeeid values to tell me when to create a new cell or table row. This works as long as there aren't ZERO employees; I would need a NULL employee name for that I think? Am I completely on the wrong track?
Thanks in advance for any ideas!
