Question 252644 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Return Category From Another Table by Comparing The Value with Min and Max

I am trying to get a column which specify the points by comparing the value from original table to min and max value from another table. I have a main table, which consists of few columns of values and the respective point column to be inserted. Here is the example:

main table

point table

expected table

I tried a few ways that i found in here, but most not as complex as mine so i have no idea how to move from here.

This is the code that i tried

SELECT a.User, a.Department, a.BU, a.Revenue, b.Point as Revenuepoint
FROM Sales a
JOIN Point b ON a.Revenue BETWEEN b.fromvalue and b.tovalue
Tags:

2

Answers


  1. 0

    Seems like both of your tables not joined correctly and condition should come after WHERE statement, there is no WHERE in your query. Try below code.

    `SELECT a.User,a.Department, a.BU, a.Revenue, b.Point as Revenuepoint
FROM Sales a
INNER JOIN Point b ON a.Department = b.Department
WHERE a.Revenue BETWEEN b.fromvalue AND b.tovalue`
    Login or Signup to reply.
  2. 0

    In below way you can add any column to the select (Column should be in a or b in your scenario)

    `SELECT a.User,a.Department, a.BU, a.Revenue, b.Point as Revenuepoint,a.Profit
FROM Sales a
INNER JOIN Point b ON a.Department = b.Department
WHERE a.Revenue BETWEEN b.fromvalue AND b.tovalue`

    Since you’re only looking for a.Revenue between b.fromvalue and b.tovalue`, there is no need to use union, Simple select query with the profit column will solve your problem.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search