Mysql – Need help to compare pricing using SQL queries

mysql-5.7query

Thank you for help in advance.

I have 2 sets of data that show bus journeys and there relevant price from 2 different systems that i need to compare.

Table1
-Brand
-Origin
-Destination
-Price

Table2
-Brand
-Origin
-Destination
-Price

So what i need todo is to match all the following fields and return the price from table2 which i have done using the following:

SELECT Table1.Brand ,
       Table1.Origin,  
       Table1.Destination,  
       Table1.Price,  
       Table2.Price 
FROM Table1  
  LEFT JOIN Table2  
         ON Table1.Brand = Table2.Brand  
        AND Table1.Origin = Table2.Origin  
        AND Table1.Destination = Table2.Destination

ResultTable: 

Brand  |  Origin  |  Destination  |  Price  |  Table2 Price  |

So up to now I have the correct data however i need to the following which i could really do with some help:

  1. Compare Table1.Price against Table2.Price and display the difference in the ResultTable above

  2. Filter out all prices that are the same so all we have left is the different prices

  3. A new query where we compare the Brand, Origin and Destination and if any are missing from table 2 we display those

Sorry for the long winded explanation but just thought i would try to make it as explicit as possible.

Best Answer

Disclaimer, everything is nullable in your tables so IS NULL predicates may give false positives.

Your query comes a long way, all you need is to add certain predicates:

1 and 2.

SELECT Table1.Brand ,
       Table1.Origin,  
       Table1.Destination,  
       Table1.Price,  
       Table2.Price,
       Table1.Price - Table2.Price as diff 
FROM Table1  
  LEFT JOIN Table2  
         ON Table1.Brand = Table2.Brand  
        AND Table1.Origin = Table2.Origin  
        AND Table1.Destination = Table2.Destination
WHERE Table1.Price - Table2.Price <> 0;

3 . 

SELECT Table1.Brand ,
       Table1.Origin,  
       Table1.Destination,  
       Table1.Price,  
       Table2.Price,
       Table1.Price - Table2.Price as diff 
FROM Table1  
  LEFT JOIN Table2  
         ON Table1.Brand = Table2.Brand  
        AND Table1.Origin = Table2.Origin  
        AND Table1.Destination = Table2.Destination
WHERE Table2.Price IS NULL;
Related Question