How to show match and unmatch SQL?

user_8889428 0
Viewed 191

I'm trying to get all records from two tables and add status match or mismatch. I try this code in MySQL but this code only show one record instead of all records

SELECT 
  id, source, destination, amount, CASE 
    WHEN COUNT(*) > 1 THEN "MATCH" ELSE "MISMATCH" END AS "status" 
FROM 
  (
   SELECT 
    temporary1.id, temporary1.source, temporary1.destination, temporary1.amount
      FROM temporary1
   UNION ALL 
   SELECT temporary2.id, temporary2.source, temporary2.destination, temporary2.amount 
      FROM temporary2
   ) compare  ORDER BY `id` ASC

could you show where is my mistakes?

@EDIT I'm sorry for not giving detail explanation. I have two tables with same columns. The column's names are id, source, and destination.

for example record in temporary1 table

+----+--------+-------------+--------+
| id | source | destination | amount |
+----+--------+-------------+--------+
|  1 | Adam   | Helen       |    100 |
|  2 | Mai    | Dan         |    200 |
+----+--------+-------------+--------+

and from ```temporary1`` table is

+----+--------+-------------+--------+
| id | source | destination | amount |
+----+--------+-------------+--------+
|  1 | Adam   | Helen       |    100 |
|  2 | Marina | Daniel      |    400 |
+----+--------+-------------+--------+

The result from query should be like this

+----+--------+-------------+--------+----------+
| id | source | destination | amount |  status  |
+----+--------+-------------+--------+----------+
|  1 | Adam   | Helen       |    100 | MATCH    |
|  2 | Mai    | Dan         |    200 | MISMATCH |
|  2 | Marina | Daniel      |    400 | MISMATCH |
+----+--------+-------------+--------+----------+

status column's value will be MATCH if record from table temporary1 and temporary2 exists in both tables, otherwise result will me MISMATCH. When I run previous query, it give the same structure that I want. but only show 1 record.

Database is MySQL MariaDB ver 10.4.12. I'm still new in database. I don't know much the difference between SQL database. So I thought all SQL database can run same query.

mysql sql
2 Answers

You seem to just need GROUP BY:

SELECT id, source, destination, amount, 
       (CASE WHEN COUNT(*) > 1 THEN "MATCH" ELSE "MISMATCH" END) AS "status" 
FROM ((SELECT temporary1.id, temporary1.source, temporary1.destination, temporary1.amount
       FROM temporary1
      ) UNION ALL 
      (SELECT temporary2.id, temporary2.source, temporary2.destination, temporary2.amount 
       FROM temporary2
      )
     ) compare 
GROUP BY id, source, destination, amount
ORDER BY `id` ASC
user_11440350

You need to add GROUP BY clause in your SQL since you are computing count(1).

sample SQL -

select id, source, destination, amount, CASE WHEN ccount > 1 THEN "MATCH" ELSE "MISMATCH" END AS "status" 
from (SELECT id, source, destination, amount, COUNT(*) as ccount 
FROM (SELECT temporary1.id, temporary1.source, temporary1.destination, temporary1.amount FROM temporary1
UNION ALL SELECT temporary2.id, temporary2.source, temporary2.destination, temporary2.amount 
FROM temporary2) 
group by id, source, destination, amount)

However I would recommend you to use INTERSECT, MINUS and UNION to get matched records and unmatched records.

Following SQL will return MATCHED records -

SELECT temporary1.id, temporary1.source, temporary1.destination, temporary1.amount, 'MATCHED' FROM temporary1
INTERSECT
SELECT temporary2.id, temporary2.source, temporary2.destination, temporary2.amount, 'MATCHED'
FROM temporary2;
user_114209840
Related