Joining Two Tables to Get Specific Results...

[RedHead]

I use MySQL version 5.5  I think I am joining these tables correctly but am finding it difficult to determine which clauses to use to get the results I need.

 

Here's the problem I'm trying to complete:

 

Find the employees who work in the Headquarter and Research departments by joining employee and department tables. Your result should be like this:

+----------+--------------+

| fname | dname |

+----------+--------------+

| James | Headquarters |

| John | Research |

| Franklin | Research |

| Ramesh | Research |

| Joyce | Research |

+----------+--------------+

 

I know my syntax is screwy, but this is what I tried (in several different forms):

 

mysql> select fname, dname

-> from employee join department

-> on employee.dno = department.dnumber

-> group by department.dname 'Headquarters', 'Research';

 

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near ''Head

quarters', 'Research'' at line 4

 

Any help with constructing the correct syntax for the outcome noted above would be appreciated.   

 

THANKS!

 

[cpd]

You should group by columns only. You do not need to specify what values to group by. The MySQL Server will do this automatically. 

[RedHead]

Thanks, CPD!  I had to switch gears and work on another assignment, but I plan to work on the syntax tomorrow.  Hopefully I'll figure it out.