CODEWITHSUNDEEP
mysql
Joseph
Joseph

Reputation: 733

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?

Upvotes: 0

Views: 43

Answers (3)

Keyur Buch
Keyur Buch

Reputation: 41

SELECT code,  name ,  parent_code,status, child
from tblA
WHERE child = 'Y' and status = 'PENDING'

pl. try i am not sure about your resultset

Upvotes: 0

Jens
Jens

Reputation: 69495

A self join should do the work:

SELECT child.*
from tblA child join tblA parent on child.parent_code =parent.code
WHERE parent.child = 'N' and parent.status = 'complete'

Upvotes: 1

KaeL
KaeL

Reputation: 3659

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'

Upvotes: 1

Related Questions