0

I want to use SQL multiplication from other table MS access in VB.NET

SELECT EC.EMPID,E.NAME,EC.SKILL/(select MIN(MINSKILL.SKILL) 
     FROM tblEmployeeCriteria MINSKILL)*100 as SKILL 
FROM tblEmployeeCriteria EC 
INNER JOIN tblemployee E ON EC.EMPID = E.EMPID

tblemployee

EMPID NAME
10037 Tamendran
10046 Ponisan
10085 Vanu

tblEmployeeCriteria

EMPID SKILL
10037 90
10046 80
10085 50

tblWeightingCriteria

ID WCSKILL WCEXPERIENCE
1 81 71

Result from SQL now :

EMPID NAME SKILL
10037 Tamendran 180
10046 Ponisan 170
10085 Vanu 100

EXAMPLE VALUE SKILL FROM TAMENDRAN : 90/50*100=180

Desired Result :

EMPID NAME SKILL
10037 Tamendran 145.80
10046 Ponisan 137.70
10085 Vanu 81

EXAMPLE VALUE SKILL FROM TAMENDRAN : 90/50*100 * 81/100=145.80

Wahlstrommm
  • 684
  • 2
  • 7
  • 21
roy
  • 693
  • 2
  • 11
  • 2
    Emulate what I did for your original question: why don't you simply add `*(select WCSKILL/100 from tblWeightingCriteria)`; assuming there is only one row in this table (if not make sure that you have the appropriate WHERE condition to reduce to the criteria row you are interested in) – tinazmu May 09 '23 at 05:14
  • @tinazmu , Your answer went perfectly. Please you answer as an answer so I can mark your answer – roy May 09 '23 at 06:30
  • Hi @user18, it is really a duplicate of your previous question, and we can leave it as is so that the site is clean – tinazmu May 09 '23 at 06:33
  • @tinazmu , Actually there is a little different because there is the addition of another table – roy May 09 '23 at 06:44
  • @user18 Be careful that it doesn't do integer arithmetic where you don't intend it, as that will make the divisions appear to give the wrong result: [Integer division in sql server](https://stackoverflow.com/q/3443672/1115360). – Andrew Morton May 09 '23 at 11:33
  • @tinazmu , I have a recent post maybe you can help me. [link](https://stackoverflow.com/questions/76209949/how-to-create-a-descending-sql-for-the-column-total-and-add-the-column-rank?noredirect=1#comment134395365_76209949) – roy May 09 '23 at 15:00
  • @AndrewMorton , Thank you for your suggestion, but I have calculated the value so the value is in accordance with the actual calculation – roy May 10 '23 at 03:12

0 Answers0