1

I have a "users" table in my database, unfortunately there is a lot of mess in it and I try to move it to new one. But just exact things not copy / paste whole.

Here is example how db looks like:

    -------*/*------------*/*------------*/*------------*/*------------*/*------------
      id        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 you see here that there are some "Organizations" which have 0 users so are absolutley useless, there are some organizations which have users but they are not active ( inactive , deleted... ).

My question is how to write a select statement to get ALL ORGANIZATION WHERE there is atelast 1 active person inside of it.

RealData is a bit more complicated but I try something like this:

UPDATE users set org_status=1 WHERE (select count(STATUS) FROM users WHERE users.MEMBERKIND="Person" AND users.STATUS="Active" AND users.ROOTORGANIZATIONUSERID= users.ROOTORGANIZATIONIDCORRECTED)>0 AND MEMBERKIND = "Organization" AND LEVEL=2

I create new row "org_status" and set it to NULL and after that I try to update it

Andurit
  • 5,612
  • 14
  • 69
  • 121
  • Pleas provide the query/queries you have tried so far. But a subselect should work, I guess (untested): `SELECT * FROM orgaTable WHERE (select count(*) from orgaTable AS users WHERE users.parentId = orgaTable.id) > 0;` – Dominik Sandjaja Sep 18 '15 at 11:24
  • possible duplicate of [How to create a MySQL hierarchical recursive query](http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – vhu Sep 18 '15 at 11:32

2 Answers2

2
select t1.*
from your_table t1
join your_table t2 on t2.parentId = t1.id
where t2.status = 'active'
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • Hey, I am sorry if I confuse you but there is not 2 tables, it's just one atm – Andurit Sep 18 '15 at 12:19
  • Yes. I join the same table (`your_table`) twice in the query with different alias names `t1` and `t2` – juergen d Sep 18 '15 at 12:20
  • Hey, thanks for point , I try it multiple times unfortunately I wasn't able to get succesfull result. I get only 24 rows rest few thousands are lost. Logicaly this looks fine so I have no clue where is aproblem – Andurit Sep 18 '15 at 12:52
  • Maybe you could build a minimal example on http://sqlfiddle.com then we can see what the problem is. – juergen d Sep 18 '15 at 12:55
1

Assuming that you only care about persons belonging to an organization with no intermediate levels, then you can get the organizations using exists:

select o.*
from users o 
where exists (select 1
              from users p
              where p.parentid = o.id and
                    o.kind = 'Org' and
                    p.kind = 'Person' and
                    p.status = 'active'
             );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786