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.
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
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;