Need help to compare pricing using SQL queries
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
Table1.Origin = Table2.Origin
Table1.Destination = Table2.Destination
ResultTable:
Brand | Origin | Destination | Price | Table2.Price |
So upto now i have the correct data however i need to the following which i could really do with some help:
- Compare Table1.Price against Table2.Price and display the difference in the ResultTable above
- Filter out all prices that are the same so all we have left is the different prices
- 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.
Thanks
Created a couple of tables and did a sql statement. Hope this helps.
work_table_t1
brand price
1 10
2 8
work_table_t2
brand price
2 8
select t1.brand,t1.price,t2.price, sum(t1.price-t2.price) as 'price diff' from work_table_t1 t1
left join work_table_t2 t2 on t2.brand = t1.brand
group by t1.brand,t1.price,t2.price
having sum(t1.price-t2.price) <>0
Results: only one record will show in results because of the "<> 0" clause
brand price price price diff
1 10 9 1
