0

I am a beginner in MySQL and i want to do a check for values but within a range, i have this: 

SELECT t1.width, COUNT( t1.width ) 
FROM test t1
INNER JOIN (
  SELECT t2.width
  FROM test t2
  GROUP BY width
  HAVING COUNT( t2.width ) >1
)t2 ON t1.width BETWEEN (t2.width +1000) AND (t2.width -1000)
ORDER BY t1.width

So what i want to do is to check if there is two values of 'width' with a difference of +1000 or -1000. The result is always null. could you please tell me what is wrong with the query?

frraj
  • 27
  • 5

1 Answers1

0

I don't fully understand what your data is. The way I understand is you are looking to see if two values from two columns have a specific difference, i.e the first value in the first column is 2000 and the first value in the second column is 1000, since there is a difference of 1000 you want this noted. You could use the CASE function (more detail here https://www.w3schools.com/sql/func_mysql_case.asp).

Say you have one column called width_1 which consists of different values of widths, and a second column called width_2 which also consists of different width values, all contain in a table called width_table, you could use the following:

SELECT
CASE
    WHEN width_1 - width_2 = 1000 OR width_1 - width_2 = -1000 THEN TRUE
    ELSE FALSE
END AS column_name
FROM width_table ;

This will produce a column whose entries are either 1 if the difference is exactly +1000 or -1000, or 0 if the difference is anything else.

If you want to check if the difference is between 1000 and -1000, then you can use the following:

SELECT
CASE
    WHEN width_1 - width_2 BETWEEN -1000 AND 1000 THEN TRUE
    ELSE FALSE
END AS column_name
FROM width_table ;
Violet Flare
  • 162
  • 1
  • 8
  • I want to see if two values from the same column have a specific difference – frraj Apr 10 '18 at 09:46
  • Sorry, I misunderstood the question. Unfortunately I'm not sure how this is done. I've found this question asked previously about finding the difference between two values in a column, hopefully this will help https://stackoverflow.com/questions/14857159/mysql-difference-between-two-rows-of-a-select-statement – Violet Flare Apr 10 '18 at 10:04