SQL Query

Hey Guys,

I'm still learning and experimenting with SQL, I would like to do the following in a single SQL query. I can change the table layout and create a new table if I have o but would prefer not to!

I have a table, part of which looks similar to:


ID---Surname---Firstname--LinemanagerID
1----Jones-----Fred-------3
2----Smith-----John-------3
3----Big-------Boss-------4
4----Top-------Boss-------5


I'm trying to produce an output something similar to:


LineManager---Surname---Firstname
Big Boss------Jones-----Fred
Big Boss------Smith-----John
Top Boss------Big-------Boss


Effectively a report listing all line managers (by name) and the staff they manage. I can do it easily by displaying the ID of the line manager and then the names of the staff they manage but I'm struggling to get the line managers name to appear? I don't know if this is making sense but I'm having trouble querying the same table twice. I'm not even sure how to phrase the query for Google! :confused

Any ideas? Thanks!

 

You shouldn't need to query the table twice. Read all the values into an array, then manipulate the data in that array.

 

I played with it a little bit and this doesn't look like it should be that difficult, but it is escaping me.

An easy solution (some dba's might yell at me for this) would be to make a manager's table with the ID and manager's name and then join the two tables on the managers.ID and table.LineManagerID in your query. Make sense?

 

I agree, it doesn't look like it should be difficult but can't make it work. :confused

I admit, as well as being efficient, I was trying to be lazy and get it working in a single statement so it would be easier to feed the result into a gridview. :-D

I was also trying to use the main staff table to ensure that if the name is changed, it is consistent throughout the system. I can put some code in to keep the tables in sync if I do create a new table but again, it's not ideal.

Thanks for trying though, I appreciate the effort.

 

You can use triggers on the tables to make sure a change in one field in a table triggers a change in the matching fields in the other table. The trigger would just have to look for updates to either of the name fields in your primary table and then concatenate the two fields as the name field in the manager's table. If you need help working out the trigger, I can help with that.

Also, using the two table approach and joining them will still give you only one output with jus one select statement.

I will try again when I have a chance to sit down in front of a mysql interface. These things are usually easier to work out if you can see the errors and outputs you get when they are wrong.

 

I never realised I could use triggers within the database itself. (I did say I was new, I'm better at the .net side!). I was thinking I would have to put the code in any asp page that modifies the staff table.

I've looked into triggers and I think they'll do the job OK. As you said, I can then join the two tables in my query and produce the output as desired. I like the idea of being able to get everything out in one query because I can then easily use a gridview to handle the sorting by a particular manager, site etc!

I'm done for tonight but will have a play with the test system tomorrow and hopefully will be able to make any changes to the live system at the weekend.

Thanks again!

 

Stop, you don't have to change your data model. I just got around to actually playing with mysql and it was clear today. I created the following table:

Code:

mysql> describe testtable;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | NO   | PRI |         |       |
| surName   | varchar(25) | NO   |     |         |       |
| firstName | varchar(25) | NO   |     |         |       |
| managerId | int(11)     | NO   |     |         |       |
+-----------+-------------+------+-----+---------+-------+

and then populated it with a few entries:

Code:

mysql> select * from testtable;
+----+---------+-----------+-----------+
| id | surName | firstName | managerId |
+----+---------+-----------+-----------+
|  1 | smith   | bob       |         3 |
|  2 | jones   | ben       |         3 |
|  3 | boss    | big       |         4 |
|  4 | honcho  | head      |         5 |
+----+---------+-----------+-----------+

And then you can just call the table twice in your select to get the output you wanted like this:

Code:

mysql> select concat(t1.firstName, ' ',  t1.surName) as 'Manager', t2.surName as 'Last Name', t2.firstName as 'First Name' from testtable t1, testtable t2 where t2.managerId = t1.id;
+-------------+-----------+------------+
| Manager     | Last Name | First Name |
+-------------+-----------+------------+
| big boss    | smith     | bob        |
| big boss    | jones     | ben        |
| head honcho | boss      | big        |
+-------------+-----------+------------+

Sorry, I don't know why I couldn't work that out without creating the table -- I must have been in a fog yesterday. Hope you didn't make any unnecessary changes.

Let me know if this works for you.

 

Hi Publius,

Sorry, I should have got back to you earlier, been deep in code most of the weekend, now just gone midnight here and all the tested code is uploaded and (hopefully) ready for tomorrow morning!

I did read your reply on the phone before I changed the database itself so while I didn't change anything, I have now learned about triggers!

The idea of referencing the same table with different names is exactly what I needed! I understand it's difficult to do without the tables and code in front of you which is why I doubly appreciate your effort in helping me. I realised I was in trouble when I couldn't even work out how to phrase exactly what I needed for a Google search!

Many, many thanks!

Danny.

 

Glad to help, and glad I got you the right answer before you listened to my first one.

Triggers are a great way to handle operations that you want to occur on any change to a table by a system. It is more efficient than making sure you include code on every page of an interface that might make an update or delete to a table.

You can also look into stored procedures as another way to put some of the code on the database side, rather than the interface side.

Neither triggers nor stored procedures should be considered a solution to every problem -- and you should make sure you document them in case you leave your current position and someone needs to take over -- but they do make things more efficient and migratory in many cases.

As a disclaimer, I am not a computer professional. I am a scientist that educated myself about this about a decade ago because I was having trouble finding the right system to track high throughput processes in the lab. The IT guys didn't always understand the needs of the laboratory and I didn't always understand (and still don't sometimes) the IT capabilities they could offer. A very understanding IT professional and myself decided to try to meet in the middle and it worked out. So caveat emptor (even if the advice is free )