CODEWITHSUNDEEP
sqlpostgresql
samba
samba

Reputation: 3091

Select unmatching rows from two tables grouping by two columns

I have two tables with sales information that have different number of rows and I want to get these rows. An important thing to notice is that records are added to the tables by the key of two columns: sale_type and sale_date.

So I think I should group by these columns after making a Union of the two tables. And filter by count. But my current solution does not work. How should I fetch the unmatching records correctly?

Here is what I've tried:

SELECT * FROM
(SELECT * FROM sales_copy
UNION ALL  
SELECT * FROM sales)
GROUP BY sale_type, sale_date
HAVING count(*)!=1;

Upvotes: 0

Views: 50

Answers (2)

Kobi
Kobi

Reputation: 2524

You can use both EXCEPT and UNION operator :

SELECT sale_type, sale_date FROM sales EXCEPT SELECT sale_type,sale_date FROM sales_copy
UNION
SELECT sale_type, sale_date FROM sales_copy EXCEPT SELECT sale_type,sale_date FROM sales

It returns rows from sales wich are not in sales_copy and rows from sales_copy wich are not in sales

The same thing can be achieved with a full join by filtering rows wich are matching:

SELECT    ISNULL(sales.sale_type, sales_copy.sale_type) AS sale_type
        , ISNULL(sales.sale_date, sales_copy.sale_date) AS sale_date
FROM      sales
FULL JOIN sales_copy
       ON sales.sale_type = sales_copy.sale_type
      AND sales.sale_date = sales_copy.sale_date
WHERE     sales.sale_type IS NULL
       OR sales_copy.sale_type IS NULL

Upvotes: 1

Barry Piccinni
Barry Piccinni

Reputation: 1791

To select the difference between two tables, I've managed to do this successfully using a full join. So in your case, you would want something like: 

SELECT S.*, SC.*
FROM sales AS S
FULL JOIN sales_copy AS SC ON (S.sale_type = SC.sale_type) AND (S.sale_date = 
SC.sale_date
WHERE (S.sale_type IS NULL AND S.sale_date IS NULL) OR (SC.sale_type IS NULL AND 
SC.sale_date IS NULL)

The result of this will select all the rows which are in one table only, ignoring the rows which are in both.

See it in action here: SQL Fiddle

Upvotes: 1

Related Questions