Join help (we've all got to start somewhere)

[leenoble_uk]

A little background:- I'm not entirely new to MySQL and have developed a number of sites already but I know they may not necessarily be optimised the best way possible. Which brings me to my current problem. I can extract this information using two queries but I know it must be possible using just one. I've studied the JOIN section of the MySQL manual and looked at a few tutorials but I can't get my head around it. Here's the situation.

I have two tables one with a complete employee list for a group of companies each employee referenced by unique id. The other table contains a description of each department/branch. What I want is a complete list of employees in one department when given the id number of a particular employee at that department.

Table 1 (id int(4), departmentid int(3), firstname varchar(20), lastname varchar(20))

Table 2 (departmentid int(3), departmentname varchar(30))

So given the number of one employee, say no 15 I want to list every employee at his department, but I do not have the department number to hand but it's obviously in employee 15's record. How do I SELECT firstname, lastname FROM employees WHERE employees.departmentid = WHAT departmentid of employee 15 IS? in one statement?

Actually, I just realised all the necessary information is all in table 1 but the info still needs joining i think. However there is in actual fact more infomation I need to extract from these tables and I've cut the columns down to the lowest number to get my point across, but if you could assume the two tables are neccessary that would be a good thing.

Any help would be appreciated.
Lee

[nicc777]

Hi

There are two things I can add as a thought, rather than a solution.

First of all, this problem whould probably require a nested SELECT statement, and the current stable 3x databases don't support nested statements. For compatibility/portability I always force myself to use only 3x features, and therefor I develop on a 3x platform. I suggest most MySQL DBA's do the same.

On the other hand, when you look at the sample problem, it actually becomes easy, because the information you require from a second/nested SQL query should already be available. Why? In whatever application you use - HTML/Compiled/Interpreted - you will have to let the user 'choose' an employee, which means you had to run a query to build the selection. In HTML, your selection would look like this:

<select name="deptid">
  <option value="$deptid">$surname, $name</option>
</select>

Note: The $deptid and $surname and $name are variables in the application, which will loop through the results from your query. The query itself looks something like:

SELECT departmentid,firstname,surname FROM employeetable

So, after the selection is made, your result will be a simple:

SELECT a.id,a.firstname,a.lastname,b.departmentname FROM employeetable AS a, departmenttable AS b WHERE a.departmentid = b.departmentid AND b.departmentid = '$deptid'

You could of course group and order as you like. As you mentioned, the join wasn't needed, but I included it anyway. The point is, that as you go through the application, you keep track of other bits of information, that can reduce the number of overall queries in the end.

This specific problem I felt was more an application design issue, then a SQL issue, but I hope it still made some sense.

As far as JOINs are concerned, I always try to keep it as simple as possible. For example, when building the user interface, you may want to include the department details as well:

<select name="employeeselect">
  <option value="$id">$surname, $name [ $deptname ]</option>
</select>

And here you CAN use a JOIN:

SELECT a.firstname,a.lastname,b.departmentid,b.departmentname FROM employeetable AS a, departmenttable AS b WHERE a.departmentid = b.departmentid

Note: Once again, I leave the group by and sort by to you.

I tackle the problem like this:

1) What is the primary table that holds my info? [ employeetable ]

2) What other table(s) hold the secondary info I require? [ departmenttable ]

3) What is the glue, or common field? [ departmentid ]

4) Construct SQL: the primary table always goes first, then the others ( I try to put them in ranking order of importance ), and I assign a single alpha character to each table [ tablename AS a, tablename AS b etc... ], and then follows the WHERE clause which I always start with the glue [ a.departmentid = b.departmentid ] and the rest of the criteria follows [ b.departmentid = '$deptid' ]

I found when I follow the above 'logic', I could always achieve success - well, not always, but close enough

I hope this helped somewhat.

Cheers

PS: Also refer to http://forums.databasejournal.com/sh...threadid=30934

[leenoble_uk]

Yeah I suppose it would make things easier to pass the department as well but I thought it would at least be possible without it.
I already have various sections on the site which limit employee selections to departments but in this case I was trying to limit the number of variables being flung about.
The page in question is for editing of client details (table 3!!) which have a particular Account Manager (employee id) assigned to them. I pass the employee id to another PHP function and write out the <select> form element for changing the assigned AM and compare the list of Account Managers to the passed id gained from the client list table.

"<option value='id'".($id == $result['id']?"selected":"").">$result['name']</option>"

I guess I was trying to be too clever. I already have the department number in a session varaiable so I could use it anyway without performing another query. I'd still like to understand JOIN queries at some point though :-)

Thanks anyway
Lee