CODEWITHSUNDEEP
mysql
Andurit
Andurit

Reputation: 5762

MySQL SELECT with another SELECT inside

I have a bit crazy structure in my database, where users and orgniations are in same table ( example below)

They are both connected with PARENTID and MEMBERID, what I need to get are: GET name of all "Person" , their MEMBERID and also name of their parent

Result should looks like:

UserName1 | DHAD781  | OrgName1

Example of table:

Here is example how db looks like:

    -------*/*------------*/*------------*/*------------*/*------------*/*------------
    MEMBERID      level          name           kind         status      PARENTID
    -------*/*------------*/*------------*/*------------*/*----------*/*------------
    EMD123F |     2        |   OrgName1   |     Org      |            |  rootID
    ---------------------------------------------------------------------------------
    DHAD781 |     3        |   UserName1  |    Person    |   active   |  EMD123F 
    ---------------------------------------------------------------------------------
    7AJIZU7 |     3        |   UserName2  |    Person    |   active   |  EMD123F 
    ---------------------------------------------------------------------------------
    DME123F |     2        |   OrgName2   |     Org      |            |  rootID
    ---------------------------------------------------------------------------------
    TT5451AL|     3        |   UserName3  |    Person    |   active   |  DME123F 
    ---------------------------------------------------------------------------------
    RRMI7481|     2        |   OrgName3   |     Org      |            |  rootID
    ---------------------------------------------------------------------------------
    PPUNSAD9|     2        |   OrgName4   |     Org      |            |  rootID
    ---------------------------------------------------------------------------------
    GJASDNZB|     3        |   UserName4  |    Person    |  inactive  |  PPUNSAD9
    ---------------------------------------------------------------------------------
    KJNSCZM7|     2        |   OrgName5   |     Org      |            |  rootID
    ---------------------------------------------------------------------------------
    1UZGOPAS|     3        |   UserName5  |    Person    |   deleted  |  KJNSCZM7
    ---------------------------------------------------------------------------------

What I did try:

SELECT t1.NAME, t1.MEMBERID 
    FROM roles t1 
    inner join roles t2 on t2.PARENTID = t1.MEMBERID
    WHERE t1.kind= 'Person'

What I get from this query: 0 results found

Upvotes: 1

Views: 67

Answers (1)

xlecoustillier
xlecoustillier

Reputation: 16351

Seems like it's the other way around:

SELECT persons.NAME, persons.MEMBERID, orgs.NAME, orgs.MEMBERID
    FROM roles persons 
    INNER JOIN roles orgs on persons.PARENTID = orgs.MEMBERID
    WHERE persons.kind = 'Person'

Also, choosing explicit aliases makes the query more readable (and less error prone :) )

Upvotes: 1

Related Questions