CODEWITHSUNDEEP
mysql
shawn michael
shawn michael

Reputation: 11

MYSQL - Selecting one different value, and one the same from same table

I have one table, with different dates, and similar other parameters. It looks likes this :

ID - Date - Domain Name
1 ! 02-20-2013 ! google.com
2 ! 02-20-2013 ! yahoo.com
3 ! 02-20-2013 ! whoknows.com
4 ! 02-20-2013 ! NULL
5 ! 04-25-2013 ! yahoo.com
6 ! 04-25-2013 ! something.com
7 ! 04-25-2013 ! google.com
8 ! 04-25-2013 ! NULL
9 ! 04-25-2013 ! google.com

I want to run a query that will find the DOMAIN NAME values that were in 02-20-2013 that are now also showing in 04-25-2013 (not NULL or empty values ofcourse)

So the output would be :

5 ! 04-25-2013 ! yahoo.com
7 ! 04-25-2013 ! google.com
9 ! 04-25-2013 ! google.com

Upvotes: 1

Views: 55

Answers (2)

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

SELECT m1.* 
FROM mytable m1
WHERE m1.date = '2013-04-25' 
AND EXISTS (SELECT 0
            FROM mytable m2
            WHERE m2.`Domain Name` = m1.`Domain Name`
            AND m2.`Domain Name` is not null
            AND m2.`Domain Name` != ''
            AND m2.date = '2013-02-20')

Result:

| ID |                         DATE | DOMAIN NAME |
---------------------------------------------------
|  5 | April, 25 2013 00:00:00+0000 |   yahoo.com |
|  7 | April, 25 2013 00:00:00+0000 |  google.com |
|  9 | April, 25 2013 00:00:00+0000 |  google.com |

Upvotes: 1

Sam Dufel
Sam Dufel

Reputation: 17598

You'll have to join the table to itself.

SELECT t2.* FROM MyTable t1 
  JOIN MyTable t2 
    ON t1.`Domain Name` = t2.`Domain Name`
 WHERE t1.date = '2013-02-20'
   AND t2.date = '2013-04-25'

See this SQLFiddle

Note that "04-25-2013" isn't a standard date format - you may want to use "2013-04-25".

Upvotes: 1

Related Questions