Stenal P Jolly Stenal P Jolly - 26 days ago 8
SQL Question

How to query a SQL statement which depends on other values of same table?

I have a table with 3 columns( name, objectroot_dn, distinguishedname). Here distinguishedname is like a parent to objectroot_dn. I have to find whether for each objectroot_dn is there a child exists or not?

I can do this using the query below. It will return True if there is a child, False if there is not. But my problem is when the total dataset gets increased it takes lots of time.

For example, If the total number of row is 50,000 then it takes 10 mins for this query to complete.

Since I'm using a framework for different database, I can't index the columns.


SELECT
name,
objectroot_dn,
distinguishedname,
CASE
WHEN (SELECT count(*)
FROM (SELECT name
FROM elaoucontainergeneraldetails
WHERE objectroot_dn = dn.distinguishedname
LIMIT 1) AS tabel1) > 0
THEN 'True'
ELSE 'False'
END
FROM elaoucontainergeneraldetails AS dn
WHERE objectroot_dn = 'SOME_VALUE';


Please let me know how can I increase the speed of this query.

Thanks in advance. Appreciate all help.

Answer

You can have the same solution using left join or exists:

SELECT
      dn.name,
      dn.objectroot_dn,
      dn.distinguishedname,
      CASE
      WHEN dn_in.objectroot_dn is not null
        THEN 'True'
      ELSE 'False'
      END
    FROM elaoucontainergeneraldetails AS dn
    LEFT JOIN elaoucontainergeneraldetails dn_in on dn_in.objectroot_dn = dn.distinguishedname
    WHERE objectroot_dn = 'SOME_VALUE';