2

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.

  • Specify your DBMS, including its version. Show CREATE TABLE scripts (instead of fields list), including indices. – Akina Oct 31 at 9:10
  • Im using - AWS RDS - MYSQL (Engine version 5.7.22) I have created a DB and imported 2 CSV files into the database using DataGrip – Nav Oct 31 at 9:17
  • CREATE TABLE System1( BRAND text, ORIGIN text, DESTINATION text, PRICE double ); – Nav Oct 31 at 9:20
  • And no unique index by (BRAND, ORIGIN, DESTINATION)? If so the structure allows duplicates, and the results makes no sense in general. – Akina Oct 31 at 9:32
  • Just a tip for future referernce - when you are asked for extra information such as SHOW CREATE TABLE foo\G;, as requested by @Akina - you should add it to the question and not as a comment - this makes life easier for anyone coming after to get all the relevant information in one place rather than having to scan down the comments. You can then let the person know that you have added the requested information by replying to their comment using the @sign plus their handle (no space). Just an FYI - p.s. welcome to the forum! :-) – Vérace Oct 31 at 11:37
3

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;   
  • Fantastic guys very much appreciate both your help with my query, its working a treat! – Nav Oct 31 at 10:06
  • @Nav: If Lennart query accomplish your task please accept his answer (clicking on grey tick) – Joe Taras Oct 31 at 11:00
  • WHERE Table1.Price <> Table2.Price – Basic Oct 31 at 17:40
3

Assuming no NULL values in any field of any record, and no duplicates by (brand, origin, destination) in any separate table...

Core query:

SELECT Brand,
       Origin,  
       Destination,  
       Price,
       MAX(CASE WHEN source = 1 THEN price END) price1,
       MAX(CASE WHEN source = 2 THEN price END) price2
FROM ( SELECT Brand,
              Origin,  
              Destination,  
              Price,
              1 source
       FROM table1
       UNION ALL
       SELECT Brand,
              Origin,  
              Destination,  
              Price,
              2
       FROM table2 ) total
GROUP BY Brand,
         Origin,  
         Destination

Then add proper HAVING condition(s) if you need in some specific records only. You may add them immediately to core query, or convert it to view and select from it with proper WHERE condition(s).

For example, if you need only records which are present in only one of these two tables then add

HAVING price1 IS NULL 
    OR price2 IS NULL

Of course if you need in some specific records only you may builв more effective query using INNER/LEFT JOIN and/or WHERE EXISTS constructions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.