CODEWITHSUNDEEP
mysqlsql
Matthew Ogborne
Matthew Ogborne

Reputation: 554

Mysql return only records that have differences between two tables

This is an extension of the earlier question here

I have two tables, yesterday and today and in each table there are two columns

StockNumber, StockLevel

How do I find out

  1. Only the StockNumbers that have differences in their StockLevel's
  2. Return the affected StockNumber & StockLevel values

For example:

todays table :

StockNumber, StockLevel
ABC, 10
CBA,5
123,5

yesterdays table : 

StockNumber, StockLevel
ABC, 9
CBA,5
123,10

To return the following wi

StockNumber, StockLevel
ABC, 10
123, 5

Noticing now StockNumber "ABC" is returning todays value 10 and StockNumber "123" is also returning todays value 5 and StockNumber "CBA" there was no change and is not returned.

Many thanks,

Matt

Upvotes: 1

Views: 6700

Answers (5)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

The following query will return stocks that are either different (by StockLevel) from yesterday, or didn't exist yesterday (this is what LEFT and IS NULL are for):

SELECT Today.*
FROM Today LEFT JOIN Yesterday
    ON Today.StockNumber = Yesterday.StockNumber
WHERE
    Today.StockLevel <> Yesterday.StockLevel
    OR Yesterday.StockLevel IS NULL

Upvotes: 0

hamilton.lima
hamilton.lima

Reputation: 1920

select T.StockNumber, T.StockLevel 
from Today as T 
left join Yesterday as Y 
on T.StockNumber = Y.StockNumber 
WHERE T.StockLevel <> Y.StockLevel

play with the results here : http://sqlfiddle.com/#!2/367b0/3/0

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

This will do it for stocks that existed both days;

SELECT t.StockNumber, t.StockLevel
FROM today t
JOIN yesterday y
  ON t.StockNumber=y.StockNumber
 AND t.StockLevel <> y.StockLevel

If you (as in your earlier question) also want the new stocks from today included;

SELECT t.StockNumber, t.StockLevel
FROM today t
LEFT JOIN yesterday y
  ON t.StockNumber = y.StockNumber
WHERE y.StockNumber IS NULL 
   OR t.StockLevel <> y.StockLevel

An SQLfiddle to test both with.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is a bit tricky, because you might have different items in the two tables. I recommend a union all and group by for this:

select stockNumer,
       max(case when which = 'yesterday' then stocklevel end) as yesterdaylevel,
       max(case when which = 'today' then stocklevel end) as todaylevel
from ((select StockNumber, StockLevel, 'yesterday' as which
       from yesterday
      ) union all
      (select StockNumber, StockLevel, 'today' as which
       from today
      )
     ) t
group by stockNumber
having min(StockLevel) <> max(stockLevel) and count(*) = 2

Upvotes: 0

driis
driis

Reputation: 164291

You can join the tables on StockNumber and select those rows WHERE NOT A.StockLevel = B.StockLevel.

Upvotes: 0

Related Questions