Need help to compare pricing using SQL queries

Tags:
Multiple
MySQL
queries
SQL join

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.

0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    You can add the calculation for the difference at the end of  your select. (Table1.Price - Table2.Price) as difference. Then add a were clause to find those <> 0. Try this and the the last query you mention can be done by testing for a null value.
    134,845 pointsBadges:
    report
  • carlosdl
    So, you want to have 2 separate queries.  The first one would be the one you currently have plus the things you mention in points 1 and 2 at the end, is that right?

    Can you share why you are using an outer join in your current query?

    Based on the details you provided, the first query wouldn't appear to need an outer join, but an inner one.

    If an inner join works, for point #2 you could add an additional join condition so that only rows with different price are joined (i.e. AND table1.Price <> table2.Price).

    If you do need an outer join (please explain why), then you would need to add a WHERE clause, like Todd suggested.

    For point #1, you would have to do something like Todd suggested as well.

    For your second query (mentioned in point #3) you could use an outer join, and as Todd suggested, since the left outer join will return null for all of the table2's columns in the rows that didn't have a match, you could add a WHERE condition to check for nulls in those columns.

    You can probably do that with a subquery as well, using a NOT EXISTS condition.  Which of the 2 methods is more efficient would most likely depend on the specifics of your database.


    85,105 pointsBadges:
    report
  • carlosdl
    I just posted some suggestions, but my post apparently needs to be moderated, not sure why.

    Let's hope it doesn't take too long to appear, otherwise it will be useless.
    85,105 pointsBadges:
    report
  • carlosdl
    Moderation issue fixed.

    Thanks Tom!
    85,105 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: