mardi 27 octobre 2015

Sql query on a single table with complex grouping

I have a table like this

Org
Columns   Id, isVirtualOrg, virtualOrgOwner, other stuff.

So an org can be:

  1. a virtual org (isVirtualOrg=true)
  2. belong to a virtual org (virtualOrgOwner != null)
  3. or it can be a standalone org as well (isVirtualOrg=false && virtualOrgOwner==null).

Is there anyway I can write a query such that the results will come back like this:

ID isVirtOrg    VirtualOrgOwner
1, true,        null, ... <--- virtual org
2, false,       1, ...  <-- org belonging to 1
3, false,       1, ... <-- org belonging to 1
4, true,        null ...  <--- virtual org
5, false,       4    <-- org belonging to 4
6, false,       null <---Standalone org, can be at top of list or bottom

Currently, the data is in this order, but it is just a fluke for this one group. Currently, I am doing this in 2 queries: 1 for all orgs that aren't virtual and another for just virtual orgs that are ferreted out using a 'in' and a 'distinct'.

Aucun commentaire:

Enregistrer un commentaire