Solved

return row based on values in another row

Posted on 2014-03-11
4
36 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

635 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