Question Help merging four tables into a datatable

[GMC]

I currently have a SQL database with four tables. I need to find a way to imalgamate these tables into one. The one table contains a postcode with around 15 or 16 employee numbers attached to it the people covering this area. I then have a staff table which all the employee numbers listed.

Is it possible to amalgamte my post table with the staff table i.e. calling the staff table to populate so for each postcode it will give the names of the staff member as well as there employee number.

Hope this makes sense

 

[Menthos]

It appears you've lost a couple of tables between typing your post title and description - not to worry though

Sounds as though your question is a simple data relation - your postcode and staff table both share a common piece of related data, the employee number. With that info you can run up a query... for example:

SELECT p.postcode, s.staffname, s.employeeno 
FROM postcodes p, staff s 
WHERE p.employeeno = s.employeeno

Not quite sure what you mean by "calling the staff table to populate" but I'm assuming you mean generating a recordset from a query.

Hope that helps.

 

[GMC]

Hi Menthos

This is what im trying to do however my post table contains 16 employee references i need a way of populating a table that placed each member of staffs details against there corresponding employee numbers. Im aware most of the references will need to be experessions as i cant have the same fields pulled out of the table.

 

[Menthos]

I think you're missing out some detail on what you have in your tables - are you saying that the employee number doesn't match between the relevant tables?

Give a rough example of what you have, if you can - that'll help hugely.

 

[GMC]

Hi Menthos

Thanks for your help on this by the way.

Ok i currently have four tables. Post, Branch, Staff and Zone.

My Post table contains
Postcode, District Number, then 15 additional fields all containing different employee numbers.

My Branch contains
Branch Num, Branch Name then three employee number fields for the managers of that area

Staff contains
Name, Mobile, Employee Num etc

Zone
Payref, Zone Num

I need to create a table from the above so that the new table consists of

PostCode, Employee Name, Telephone Number, Employee Number, but for all the 15 fields in the post table so id be getting

Post, Name1, Tel1, Employee Num, Name2, Tel2, Employee Num, Name3, Tel3, Employee num

Hope this makes sense

 

[Menthos]

I had a feeling you were going to come back with that.

Is there any reason that your result has to stick the employees for a given postcode into one record? Can you not have : Postcode, Name, Tel, Employee Num ? You'd make things a whole lot easier for yourself.

If you can go that route, then you could try something along the lines of concatenating your 15 fields into a single delimited field and then doing CHARINDEX to see if an individual employee number is contained in it. It wouldn't be a pretty query, but it would do the job.

 

[GMC]

The reason im trying to put all the information into one record is to allow searching and displaying of information. I have a search to create which searches against a persons name from the staff table and then pull out the corresponding postcode and employee numbers for the area they look after. Unfortunately once i have got the employee name etc at present theres no way of linking it to the post table. This is why i was hoping to link it all through and then carry out the search.

Hope this makes sense.

 

[Menthos]

Well then you don't really need to do any amalgamation, if I understand correctly, you just want to pull the postcode/district info for a given employee number, and that employee number can be in any one of the 15 fields, right?

Something along the lines of:

SELECT e.name, p.postcode 
FROM postcodeTable p, employeeTable e 
WHERE CHARINDEX('|' + CONVERT(varchar, e.employeeno) + '|', '|' + convert(varchar, p.field1) + '|' + convert(varchar, p.field2) + '|' convert(varchar, p.field3) + '|' + etc.) > 0

Should do the trick (obviously continue adding the fields to the CHARINDEX check and also terminate with a + '|'

 

[GMC]

Thanks for your help in this mate. After doing some more tests ive concluded that ill have to go back to the team and re-work the database layouts. One of the tables is causing most of these issues as theres no clear defined way of identifying which zone applies to which postcode.