0

I have two tables below. I am looking to find all the combinations of store and newid in sales2 which are not present in sales1 and combine them with sales1.

I am looking for the end result to be:

store  newid  amount  total
 123 |  123  | 12.3 |  1
 456 |  123  | 45.6 |  2
 789 |  adsf | 78.9 |  3
 321 |  123f | NULL | NULL 
 789 |  1654 | NULL | NULL

Original Tables

CREATE TABLE sales1 (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
store  VARCHAR(30) NOT NULL,
newid VARCHAR(30),
amount DOUBLE,
total INT
);

CREATE TABLE sales2 (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
store  VARCHAR(30) NOT NULL,
newid VARCHAR(30),
amount DOUBLE
);

INSERT INTO sales1 (store, newid, amount, total) VALUES
    ('123','123', 12.3, 1),('456','123', 45.6, 2),('789','adsf', 78.9, 3);

INSERT INTO sales2 (store, newid, amount) VALUES 
    ('123','123', Null),('456','123', Null),('321','123f', Null),('789','1654', Null);
lukechambers91
  • 691
  • 1
  • 6
  • 21

1 Answers1

1
  • We can use two different SELECT queries for this. First query will fetch all the rows from sales1 table.
  • Second would fetch the remaining rows from sales2 table, which do not exist in the sales1 table. We will use LEFT JOIN from sales2 to sales1 and consider only those rows where sales1.id is NULL after the Join.
  • We will eventually use UNION ALL to combine the result-set from both the queries.

Query

SELECT
 s1.store, s1.newid, s1.amount, s1.total 
FROM sales1 s1

UNION ALL 

SELECT
 s2.store, s2.newid, s2.amount, NULL AS total  
FROM sales2 s2
LEFT JOIN sales1 s1
 ON s2.store = s1.store AND 
    s2.newid = s1.newid 
WHERE s1.id IS NULL;

Result

| store | newid | amount | total |
| ----- | ----- | ------ | ----- |
| 123   | 123   | 12.3   | 1     |
| 456   | 123   | 45.6   | 2     |
| 789   | adsf  | 78.9   | 3     |
| 321   | 123f  |        |       |
| 789   | 1654  |        |       |

View on DB Fiddle

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • full outer joins for mysql have already been covered in quite a few questions here on SO. If you think that this is what this question is about, then voting to close would be the right action to take. – Shadow Nov 20 '18 at 13:23
  • @Shadow it is "like" not specifically Full Join. For eg: take this question - https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql Answers there are combination of left and right join. while here it is not. It just looks like that. – Madhur Bhaiya Nov 20 '18 at 13:26
  • @Shadow I have removed the line causing doubts. – Madhur Bhaiya Nov 20 '18 at 13:26
  • 1
    This is full outer join, no matter whether you remove that sentence from your answer or not. Flipping around the order of the tables or using left + right joins does not make any difference at all. – Shadow Nov 20 '18 at 13:30
  • @Shadow I have turned my answer as community answer. You have the power to close it as duplicate, I believe ! – Madhur Bhaiya Nov 20 '18 at 13:32