Task on MS SQL. As an example of my problem, I cite two simple tables: the staffing table and employees. It is necessary to compare employees with staffing.
The peculiarity is that in this example, 3 posts are the same (code 32) and they need to be compared by 3 employees with these posts (code 32). The only links between the tables are the Dolby Code (Dol) and the Division Code (Otdel). The Fam field (Last Name) in the WorkTable table is unique. Id in both tables does not participate in the links, unique for the records.

We have:

Staffing table
DolTable:
Id | Otdel | Dol | Fam
431 | 4819 | 32 | 0
432 | 4819 | 32 | 0
433 | 4819 | 32 | 0

Table of workers
WorkTable:
Id | Otdel | Dol | Fam
1521 | 4819 | 32 | Ivanov
1522 | 4819 | 32 | Sidorov
1523 | 4819 | 32 | Petrov

It is necessary to display only 3 lines like this:

Id | Otdel | Dol | Fam
431 | 4819 | 32 | Ivanov
432 | 4819 | 32 | Sidorov
433 | 4819 | 32 | Petrov

No matter how I tried to make such a sample, it does not work.
Tormented request type:

SELECT * from DolTable as dt LEFT JOIN WorkTable as wt ON ((dt.Dol = wt.Dol)and(dt.Otdel = wt.Otdel))

But while I get 3 lines that repeat three times = 9 lines.

It is necessary to withdraw without repetitions.

In the future, instead of SELECT I want to do Update Fam fields in the DolTable table on the same principle.

I will try to be clearer. Added by:

Staffing table
DolTable:
Dol | Fam
32 | 0
32 | 0
32 | 0

Table of workers
WorkTable:
Dol | Fam | Name |
32 | Ivanov | Ivan |
32 | Sidorov | Sergey |
32 | Petrov | Peter |

It is necessary to display only 3 lines without repetitions like this:

Id | Otdel | Dol | Fam | Name
431 | 4819 | 32 | Ivanov | Ivan
432 | 4819 | 32 | Sidorov | Sergei
433 | 4819 | 32 | Petrov | Peter

The essence of the question is that the SELECT LEFT JOIN does a sample without repeating on the left.

  • Make GROUP BY - Arsen
  • Please tell me the code how to apply GROUP BY here for the Id field in the DolTable table. - impervald
  • And what is stored in the DolTable table? Posts? Why then is the Dol field there? - DaemonHK
  • By sabzh - either use INNER JOIN , or when using LEFT JOIN use the WHERE - DaemonHK selection condition
  • In the DolTable table, the Dol field is the position code. In the WorkTable table, this is also a post code. The decoding of job codes goes in a separate table, but not in DolTable. DolTable is a type of staffing. - impervald

1 answer 1

 select D.id, W.Otdel, W.Dol, W.Fam from ( select w.*, row_number() over(partition by Otdel order by id) RN from WorkTable w ) W left join( select Otdel, Id, row_number() over(partition by Otdel order by id) RN from DolTable ) D ON D.Otdel=W.Otdel and D.RN=W.RN

example on sqlfiddle.com

In general, it is impossible to compare by last name, it is clearly not unique. Any connection of tables must be according to their primary key, which is usually id. Therefore, on the basis of this request, the update should write to the staffing id of the employee who took this position.

  • Yes, Mike, Fam it will be like a unique employee Id. I study your code, I'll write right away, how will I figure it out. - impervald
  • Is @impervald Fam a surname? it is not unique; there may well be two employees with the same name. Get a separate numeric id. - PashaPash ♦
  • @impervald If Ivan Ivanov Ivanovich works for you and Ivanov Ivan Ivanovich decides to get another job, will you refuse him a job because his full job is already working? - Mike
  • I wrote incorrectly in the example, in Fam I have the unique Integer values ​​of the workers, it was necessary not to write all the same names, so that it does not mislead everyone. Mike, in any case, your solution is already tested and works, thank you very much! - impervald