Joseph Goh Joseph Goh - 4 months ago 11
SQL Question

MYSQL - Select Query Where condition refer to same table

I have a table that looks like :

tblA

code | name | parent_code | status | child
---------------------------------------------------------
1 | ABC | | complete | N
1.1 | ABC.1 | 1 | pending | Y
2 | BCD | | pending | N
2.1 | BCD.1 | 2 | pending | Y


I am trying to get the record with
child = Y
and its parent
status = complete


This is the output i am trying to get

code | name | parent_code | status | child
----------------------------------------------------------
1.1 | ABC.1 | 1 | pending | Y


So far, the method i am using is query all the record with
child=Y
and looping again to get the status of each
code
base on the
parent_code
from previous query.

Is it possible to make the query more simple?

Answer

Check this one.

SELECT C.*
    FROM tblA C
    INNER JOIN tblA P ON P.code = C.parent_code
    WHERE C.child = 'Y'
        AND P.status = 'complete'