CODEWITHSUNDEEP
mysqlsqljoin
L-Plate Louis
L-Plate Louis

Reputation: 33

Select records that are present on one date but not on another?

If I have a table t1 that looks this where records are unique on a combination of item, size and date;

item    size    date
1234    S   2013-02-11
1234    M   2013-02-11
1234    L   2013-02-11
9999    S   2013-02-11
9999    M   2013-02-11
9999    L   2013-02-11
1234    S   2013-02-13
1234    M   2013-02-13
1234    L   2013-02-13
9999    S   2013-02-13
9999    M   2013-02-13

How can I retrieve the item and size that is missing from 2013-02-13 when compared to 2013-02-11?

The return I am looking for is, 

item    size
9999    L

I have tried;

SELECT ta.item, 
         ta.size
FROM t1 ta 
    LEFT JOIN t1 tb 
        ON ta.item = tb.item
            AND ta.size = tb.size
WHERE ta.date = '2013-02-11'
   AND tb.date = '2013-02-13'
   AND tb.size IS NULL

and

SELECT item, size 
FROM t1
WHERE t1.date = '2013-02-11'
   AND NOT EXISTS (
                    SELECT item, size 
                    FROM t1
                    WHERE t1.date = '2013-02-13'
                  )

both return empty sets.

The only way I have managed to get this to work is by using temporary tables;

CREATE temporary table temp1
SELECT * FROM t1 WHERE date = '2013-02-11';

CREATE temporary table temp2
SELECT * FROM t1 WHERE date = '2013-02-13';

SELECT temp1.item, temp1.size FROM temp1
   LEFT JOIN temp2 
        ON temp1.item = temp2.item AND temp1.size = temp2.size
WHERE temp2.size IS NULL

Temporary tables are not going to be possible. How can I achieve this without using temp tables?

Many thanks,

(please be gentle, i am just starting out!)

Upvotes: 3

Views: 866

Answers (4)

Andriy M
Andriy M

Reputation: 77657

Others have provided valid solutions for you, and this post merely addresses your failed attempts, explaining what was missing and how it could be fixed.

This query

SELECT ta.item, 
         ta.size
FROM t1 ta 
    LEFT JOIN t1 tb 
        ON ta.item = tb.item
            AND ta.size = tb.size
WHERE ta.date = '2013-02-11'
   AND tb.date = '2013-02-13'
   AND tb.size IS NULL

fails because there's a contradiction in the WHERE clause. The query is trying to find a row where tb.size is NULL, which would mean that the tb instance didn't have a match with the ta one, and tb.date is a particular value. Now, if there's no match in tb, all its columns will be returned as NULLs, which includes tb.date, meaning it cannot be equal to a value and be null at the same time.

Obviously, your intention was different. You wanted to left-join one subset of a table to another subset (of the same table, as it happens). In that case, the condition defining the subset you are joining should be put into the ON clause. That is, you are not joining on the sole condition that both item and size columns of both instances match, but on the combined condition of those columns matching and the other instance's date being a particular value. So, this is what you should have instead:

SELECT ta.item, 
         ta.size
FROM t1 ta 
    LEFT JOIN t1 tb 
        ON ta.item = tb.item
            AND ta.size = tb.size
            AND tb.date = '2013-02-13'
WHERE ta.date = '2013-02-11'
   AND tb.date = '2013-02-13'
   AND tb.size IS NULL

The other query

SELECT item, size 
FROM t1
WHERE t1.date = '2013-02-11'
   AND NOT EXISTS (
                    SELECT item, size 
                    FROM t1
                    WHERE t1.date = '2013-02-13'
                  )

seems to be a result of confusing EXISTS with IN.

First, how EXISTS works. It returns true when its subquery returns a non-empty row set. It doesn't matter which columns, only the presence of rows counts. If none is returned, the result is false. This way, your query reads like this:

Return every row whose date is '2013-02-11' AND there aren't any rows for date = '2013-02-11'.

While you probably meant to say by that query something like this:

Return every row whose date is '2013-02-11' AND there are no corresponding row with the same item and size and with the date of '2013-02-11'.

That is, you need to correlate the EXISTS subquery with the main query, which means including the current row's size and item into the EXISTS test. So, this is what the fix might look like:

SELECT item, size 
FROM t1 ta
WHERE ta.date = '2013-02-11'
   AND NOT EXISTS (
                    SELECT item, size 
                    FROM t1 tb
                    WHERE tb.date = '2013-02-13'
                      AND ta.item = tb.item
                      AND ta.size = tb.size
                  )

On the other hand, you could rewrite the same query to use NOT IN instead of NOT EXISTS. Here:

SELECT item, size 
FROM t1
WHERE date = '2013-02-11'
   AND (item, size) NOT IN (
                            SELECT item, size 
                            FROM t1
                            WHERE date = '2013-02-13'
                           )

which basically says:

Return every row whose (item, size) pair for date = '2013-02-11' is not in the (item, size) pair set for date = '2013-02-11'.

An IN/NOT IN query may often look clearer than its EXISTS/NOT EXISTS counterpart. Whether the two would result in identical query plans is another matter, and you should probably test before deciding which method to use (but that applies to the LEFT JOIN + WHERE IS NULL method too, of course).

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Use an aggregation query:

select item, size
from t1
group by item, size
having sum(case when date = '2013-02-11' then 1 else 0 end) = 1 and
       sum(case when date = '2013-02-13' then 1 else 0 end) = 0

This is an example of a "groups within groups" query. By varying the having clause, you have a lot of flexibility in the conditions you want to meet. For instance:

having sum(case when date = '2013-02-11' then 1 else 0 end) = 0 or
       sum(case when date = '2013-02-13' then 1 else 0 end) = 0

would return combinations that are present in the data, but missing on either day.

Upvotes: 2

Nitesh Kumar
Nitesh Kumar

Reputation: 1774

You can also try this simple query

SELECT item, size FROM dbo.Table_1 WHERE date = '2013-02-11'
EXCEPT
SELECT item, size FROM dbo.Table_1 WHERE date = '2013-02-13'

Upvotes: 0

John Woo
John Woo

Reputation: 263703

SELECT  a.*
FROM
        (
            SELECT  item, size
            FROM    tableName
            WHERE   date = '2013-02-11'
        ) a
        LEFT JOIN
        (
            SELECT  item, size
            FROM    tableName
            WHERE   date = '2013-02-13'
        ) b ON  a.item = b.item AND
                a.size = b.size
WHERE b.size IS NULL

Upvotes: 4

Related Questions