CODEWITHSUNDEEP
sqlpostgresql
Jwqq
Jwqq

Reputation: 1087

How to compare records in tables in my case?

I have two tables like these

TableA

id | name   |  type | 

1    test 1    parent
2    test 3    parent
3    test 3    child
4    test 4    parent
5    test 5    child
6    test 6    child

TableB

child_id | parent_id 
  6           1
  5           2
  4           3

Basically I have two tables that have wrong records and I want to find them through queries.

I need to find the record that is supposed to be parent but have type as child. (id: 3 in my case) AND find the record that is supposed to be child but have parent as type. (id: 4 in this case)

I have tried the following query

select * from TabelB as b left join TableA as a on b.child_id = a.id WHERE a.type=parent

They would only show me the type as parent but I can’t find the bad record.

In this case, I need to find test 3 and test 4 record. I am not sure how to proceed on this. Can anyone help me about it? Thanks a lot!

Upvotes: 1

Views: 37

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

select *
from
    a
    inner join
    b on (a.id, a.type) in ((b.child_id, 'parent'), (b.parent_id, 'child'))

Upvotes: 2

Related Questions