0

Good day. I'm having a hard time figuring out how to do this:

SELECT P.GrossSalary, S.Contribution FROM Payroll AS P, SSSChart AS S WHERE
P.GrossSalary >= S.RangeStart AND P.GrossSalary <= S.RangeEnd;

I need the corresponding contribution amount from SSSChart table where the Gross Salary is between the Start and End range.

The problem is it will work on the first found matched record from Payroll table but the searching from the SSSChart table will not start from the top again for the next Payroll record, instead, will continue the search after the found record from the previous Payroll record. I tried several SQL commands but found no luck. All the help will be appreciated. (Doing this for my payroll system practice)

Christopher Bermudez
  • 1
  • Could you add an example of what exactly do u mean ? – bayblade567 Mar 29 '15 at 05:59
  • Example: the first gross salary from payroll table is 1,350 that corresponds to the first row of the SSS table(gross salary >= rangeStart and gross salary <= rangeEnd). the 2nd row of the payroll table is within the range of the first row of the SSS table. but it returns no contribution because i observed that after the first result, the searching from SSS table will continue to the next row after the first result. it will now search from the top of the SSS table again :( – Christopher Bermudez Mar 29 '15 at 06:12

1 Answers1

0

Do you want to query the entire Payroll table and find the corresponding contribution value from the SSSChart table for each result? Consider trying something along the lines of:

SELECT 
    P.GrossSalary, S.Contribution
FROM
    Payroll as P
LEFT JOIN
    SSSChart as S ON P.GrossSalary >= S.RangeStart AND P.GrossSalary <= S.RangeEnd
WHERE 
    1;

This is assuming each GrossSalary only belongs to exactly one SSSChart range.

turntwo
  • 2,452
  • 19
  • 28
  • You're on the right track sir. that's what i'm talking about. :) my command assumes that i'm looking for 1 is to 1 result. what i want is many to 1 result :D – Christopher Bermudez Mar 29 '15 at 06:17
  • Not really. It just retrieves all the entries of the table. I just prefer to have a `WHERE` statement in queries. I like everything explicit. And this also: http://stackoverflow.com/questions/1983655/importance-of-where-1-in-mysql-queries. But really, it's all preference. – turntwo Mar 29 '15 at 10:08