Solved

return row based on values in another row

Posted on 2014-03-11
4
33 Views
Last Modified: 2016-06-04
Subset of table:
id               date                           product_id
1                11/22/2012                L11
1                11/15/1990                L11.1
2                10/2/2005                  L22
2                10/4/2007                  P3
3                 2/5/2010                   L3
3                 3/2/2011                   L11
4                4/6/2012                    L22
4                6/2/1999                    L4
etc

I want to return all L11, L11.1, L22, where the date is older than two years for all of the product id's I am looking for (L11, L11.1, L22).  All  L11, L11.1, L22 have to be older than two years else nothing should be returned per id.  

For example results would look like:
id               date                           product_id
2                10/2/2005                  L22
3                 3/2/2011                   L11

Thanks!
0
Comment
Question by:needplease
  • 2
4 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39919870
SELECT * FROM table 
WHERE product_id IN ('L11', 'L11.1', 'L22') AND DATE_DIFF(NOW(), date) > 730

Open in new window

HTH,
Dan
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39919907
>older than two years
I would prefer to do this differently than what was suggested above (though that one will work:
AND date < DATE_SUB( NOW(), INTERVAL 2 YEAR )

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-sub
the explanation is that with the above suggested syntax, it will work, but will be unable to use a index on the date column.
so, if you have a index on product_id + date column, that query shall be as efficient as possible.
0
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39920669
Re-reading your question, I see my query would return an unwanted value (id 1, where you had a more recent order). See below the modified query, also incorporating Guy's suggestion:
SELECT * FROM table 
    WHERE product_id IN ('L11', 'L11.1', 'L22') 
        AND date < DATE_SUB( NOW(), INTERVAL 2 YEAR )
        AND id NOT IN (
        SELECT id FROM table 
            WHERE product_id IN ('L11', 'L11.1', 'L22') 
                AND date >= DATE_SUB(NOW(), INTERVAL 2 YEAR) )

Open in new window

0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question