Hello there I have a following table
------------------------------------------
| id | language | parentid | no_daughter |
------------------------------------------
| 1 | 1 | 0 | 2 |
------------------------------------------
| 1 | 1 | 0 | 2 |
------------------------------------------
| 2 | 1 | 1 | 1 |
------------------------------------------
| 2 | 2 | 1 | 1 |
------------------------------------------
| 3 | 1 | 1 | 0 |
------------------------------------------
| 3 | 2 | 1 | 0 |
------------------------------------------
| 4 | 1 | 2 | 0 |
------------------------------------------
| 4 | 2 | 2 | 0 |
------------------------------------------
| 5 | 1 | 2 | 0 |
------------------------------------------
| 5 | 2 | 2 | 1 |
-----------------------------------------
| 5 | 1 | 4 | 1 |
------------------------------------------
| 5 | 2 | 4 | 1 |
------------------------------------------
Scenario
Every record has more than one rows in table with different language
ids. parentid
tells who is the parent of this record. no_daughter
columns tells against each record that how many child one record has. Means in Ideal scenario If no_daughter
has value 2
of id = 1
, it means 1
should be parentid
of 2 records in same table. But If a record has more than one exitance with respect to language, it will be considered as one record.
My Problem
I need to find out those records where no_daughter
value is not correct. It means if no_daughter
is 2, there must be two records whoes parentid
has that id. In above case record with id = 1
is valid. But record having id = 2
is not valid because the no_daughter = 1
but actual daughter of this record is 2. Same is the case with id=4
Can any body tell me how can I find these faulty records?
So far help received
SELECT DISTINCT
id
FROM
tbl_info t
INNER JOIN
(SELECT
parentid,
COUNT(DISTINCT id) AS childs
FROM
tbl_info
GROUP BY parentid) AS parentchildrelation
ON t.id = parentchildrelation.parentid
AND t.no_daughters != parentchildrelation.childs
This query is returning those ids who have been used as parentid
somewhere in table but having wrong no_daughter
values. But not returning ids that has value in no_daugter
columns but have not been used as parentid
any where in table. For exampl id = 5
has no_daughter = 1
but it is not used as parentid
in table. So it is also a faulty record. But above query is not capturing such records.
Any help will be much appreciated.