Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DTFan
Yak Posting Veteran
52 Posts |
 Posted - 2006-03-29 : 09:06:38
|
Sorry about the confusing subject but I wasn't sure right about how to phrase it. I've got the following tables:tblEmployeeempID (p/k integer)empFName (v/c 35)empLName (v/c 35)...tblGroupgrpID (p/k integer)grpName (v/c 50)...tblEmpGroupegID (p/k integer)empID (f/k integer)grpID (f/k integer)tblScheduleschID (p/k integer)empID (f/k integer)schLoc (integer)schChrg (integer)schBldg (integer) Multiple employees can belong to the same group. That "group" works on a schedule BUT not all employees in that group have to be on the schedule. So a group might have 20 employees on it, but only 5 are on the schedule. Now, if they are on the schedule, they ALL have the same schedule (whether it's 1 day or 6 days ... it's always the same).In my code I have a stored procedure that, when it receives an empID, it will search the tblSchedule for any entries with that empID. As I said, sometimes it returns one record, sometimes 6, etc. Sometimes it returns none. That's where my problem lies.What I want to happen is that, if a empID returns no matching tblSchedule records, then I want to search the tblSchedule for any 1 other empID from the same group as the initial empID, and then, duplicate the records for that empID for the new one (and do the insert).Wow. That sounded confusing. So look at it this way:- I (DTFan) have no entries in the tblSchedule table.- You (YourNameHere) have 5 entries in the tblSchedule table (along with 10 others from our group).- When my empID returns no records, the new stored procedure will search for any ONE member of our group who does have records (it could be you or one of the other 10 members from our group).- When it finds you (or one of the other matching group members), it will take the 5 entries you have and do an insert into tblSchedule using MY empID (so that now you and I will have the same entries).I'm sorry that sounds so confusing. I hope I explained it clearly enough. I tried writing a stored procedure which uses LEFT JOIN but I kept getting errors. I can post what I've got (if everyone wants a good laugh). Also I kept having a problem with only getting 1 other group members records and then switching out my empID for theirs.Any idea's/suggestions/etc will be greatly appreciated. And thank you in advance for any help you can give.If it will help, I can post the database/table schema and some sample data. I just didn't want to make the post too long unless it was necessary (in case this is a real simple solution).Thank you again.DTFanEver-hopeful programmer-in-training  |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-29 : 09:19:07
|
| What would you want to do if 20 people are in the group, 5 are on one schedule and another 5 are on a different schedule..etc?? |
 |
|
|
DTFan
Yak Posting Veteran
52 Posts |
Posted - 2006-03-29 : 09:31:39
|
quote:
Originally posted by RickD
What would you want to do if 20 people are in the group, 5 are on one schedule and another 5 are on a different schedule..etc??
Right now (from what I've been told) that will not be a situation (although you know how that goes ... once it's built this way they (the powers that be) will want to change it).  But supposedly if a group is on a schedule then they are all on the "same" schedule. I'm sure it will change to where different members can have different schedules (and at that point it will, I imagine, have to be handled manually and not automatically). But for now I just have to work with what I've been told.  DTFanEver-hopeful programmer-in-training |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-29 : 10:04:18
|
| If you're sure.. lolYou will need to make sure you only bring back a single member of that groups records, to do this, select any Id that are in the same group and use a MIN() to get the first ID you come across..So you could do:Insert into table (schID,empID,schLoc,schBldg)select s.schID,2,s.schLoc,s.schBldgfrom tblSchedule swhere s.empid in (select min(empid) from tblSchedule s inner join tblEmpGroup eg on eg.empid = s.empid and eg.grpid in (select grpid from tblEmpGroup where empid = 2))which is very messy, but would work, i'm sure someone here can come up with a better solution.. |
|
|
|
DTFan
Yak Posting Veteran
52 Posts |
Posted - 2006-03-29 : 10:58:46
|
quote:
Originally posted by RickD
If you're sure.. lolwhich is very messy, but would work, i'm sure someone here can come up with a better solution..
That worked like a charm. The only edit I had to make was not selecting/insert the primary key (which is listed as an Identity field). Being that it is a PK, I just removed it and will let the db handle creating it.Thank you very, very much for the help. I'm sure that it's going to change (and with my luck, probably in the not so near future). But for right now it is exactly what I needed. Thank you again.DTFanEver-hopeful programmer-in-training |
|
|
|
|
|
|
|
|
multiple insert from matching parent table?