SQL check if child table rows have the same value as the parent table row for a particular column-CodePudding

If you have a parent table with a column representing the transaction type and the child table with rows that have the same column representing the same transaction type lines, how can I check in one SQL query that the child table doesn't have rows that have a different type entered by mistake? Parent transaction type 1 has 3 child table trans rows and one of them is not type 1 but type 2. I'd need that child row ID and the parent table row ID.

CodePudding user response：

What is the idea behind duplicating transaction_type between 2 tables? Better to have only 1 source of truth.
Let's say the schema is:

parent_transaction: id, transaction_type
child_transaction: id, parent_tr_id, transaction_type

select pt.id as parent_tr_id,
       pt.transaction_type as parent_tr_type,
       ct.id as child_tr_id,
       ct.transaction_type as child_tr_type
from parent_transaction pt
  join child_transaction ct on pt.id = ct.parent_tr_id
where ct.transaction_type != pt.transaction_type;

demo