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.
Best Answer
The problem with your provided query is that you're doing an
INNER JOIN
. Because there are no rows with a parentid of 5, the subquery would return NULL on those rows and be excluded.What you want is a
LEFT JOIN
, something like this (sqlfiddle):I included more columns, because where
id=5 and parentid=2
has two different values for no_daughter. You should be able to adjust those as needed.