MySQL for exact integer range

Question

I made some research like queries for numeric range, interval query and more, but none of the answers have gave me the correct result.

For simplicity I will put the info without using ids, the database has a proper schema but I want to create a query to obtain data that fits exact integer range.

I have this table but this info is short to clarify my problem:

+------+------+--------+---------------+
| year | make | model  | motor         |
+------+------+--------+---------------+
| 1998 | Audi | A3     | 4 Cil 1.8 Lts |
| 1999 | Audi | A3     | 4 Cil 1.8 Lts |
| 2000 | Audi | A3     | 4 Cil 1.8 Lts |
| 2001 | Audi | A3     | 4 Cil 1.8 Lts |
| 2002 | Audi | A3     | 4 Cil 1.8 Lts |
| 2003 | Audi | A3     | 4 Cil 1.8 Lts |
| 2004 | Audi | A3     | 4 Cil 1.8 Lts |
| 2004 | Audi | A3     | 4 Cil 2.0 Lts |
| 2005 | Audi | A3     | 4 Cil 2.0 Lts |
+------+------+--------+---------------+

For example I want to obtain the motor that fits 1998 - 2004 range (4 Cil 1.8 Lts), but this query give me also 4 Cil 2.0 Lts

SELECT DISTINCT motor
FROM general G
WHERE G.year BETWEEN 1998 AND 2004

Resulted table:

+---------------+
| motor         |
+---------------+
| 4 Cil 1.8 Lts |
| 4 Cil 2.0 Lts |
+---------------+

Also I want that this query returned empty if not fit exactly with the range, I created an other query trying to obtain a different result but not worked.

SELECT DISTINCT motor
FROM general G
WHERE G.year IN (1998, 2005);

Resulted table:

+---------------+
| motor         |
+---------------+
| 4 Cil 1.8 Lts |
| 4 Cil 2.0 Lts |
+---------------+

Thank you all

Update:

My problem is not related with make or model parameters, I'm struggle with year range which in this specific example is obtain this result if the range is between 1998 and 2004.

Result:

+---------------+
| motor         |
+---------------+
| 4 Cil 1.8 Lts |
+---------------+

If the year range is between 1998 and 2005, I want to obtain an empty result because in my table, data not fits exactly with this range.

O. Jones · Accepted Answer · 2014-03-14 00:28:23Z

2

It looks like you are asking "what values of the motor column appears in all years from 1998 to 2004?"

The query in your question answers a related question, which is "what values of motor appear in any year from 1998 - 2004?"

This query (http://sqlfiddle.com/#!2/53d6d/15/0) answers your question by comparing the number of years where Audi made that engine with the total number of years.

SELECT motors.years, motors.motor
  FROM (
        SELECT COUNT(DISTINCT year) AS years, motor
          FROM general
         WHERE year BETWEEN 1998 AND 2004
         GROUP BY motor
       ) AS motors 
  JOIN (
       SELECT COUNT(DISTINCT year) AS years
         FROM general
         WHERE year BETWEEN 1998 AND 2004
       ) years ON motors.years = years.years

This query (http://sqlfiddle.com/#!2/53d6d/17/0) takes a slightly different approach, by first computing the production lifetime of each engine in the table, and then comparing your date range to the production range.

SELECT motor 
  FROM (
         SELECT MIN(year) AS firstyear, 
                MAX(year) AS lastyear, 
                motor
           FROM general
          GROUP BY motor
       ) AS motors_years
  WHERE firstyear=1998 AND lastyear=2004

answered Mar 14, 2014 at 0:28

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

technology_dreamer Over a year ago

Ollie you are the man...a perfect spiritual person that help other with their problems!

Fabio · Accepted Answer · 2014-03-13 23:59:42Z

1

I think you can just add a second where condition against motor column to be like 4 Cil 1.8 Lts

SELECT DISTINCT motor
FROM general G
WHERE G.year BETWEEN 1998 AND 2004
AND G.motor LIKE '4 Cil 1.8 Lts'

answered Mar 13, 2014 at 23:59

Fabio

23.5k12 gold badges57 silver badges66 bronze badges

2 Comments

O. Jones Over a year ago

Sorry to be a little snarky, but in this case why not use the query SELECT '4 Cil 1.8 Lts' AS motor ? You need to know the answer to ask the question this way.

Fabio Over a year ago

That might be a better solution

ExactaBox · Accepted Answer · 2014-03-14 00:01:40Z

-1

Your WHERE statement probably needs to be more specific:

SELECT DISTINCT motor
FROM general G
WHERE 
  G.year BETWEEN 1998 AND 2004
  and make = 'Audi' 
  and model = 'A3'

answered Mar 14, 2014 at 0:01

ExactaBox

3,4251 gold badge22 silver badges31 bronze badges

Collectives™ on Stack Overflow

MySQL for exact integer range

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related