0

So I have a table that looks like this

ID     AccountType     Name                Individ
1      O               Acme Company        00
2      P               Joe Smith           00
1                      John Doe            01
1                      Steve Johnson       02
3      P               Shirley Johnson     00
2                      Jane Smith          01
1                      Kevin Lastname      03

So, O stands for Organization, and P stands for personal. Subaccounts are mixed in the table with the primary accounts, and a subaccount of an account has the same ID, but a null accounttype. I need to find a way to find all subaccounts of one AccountType, excluding subaccounts of personal accounts. I tried an INNER JOIN, but I can't join a column on itself.

SELECT * FROM <schema.table> 
INNER JOIN <schema.table> ON table.id = table.id 
WHERE accounttype = ('B' OR 'F' OR 'O' OR 'S') AND 
individ != '0' 
GROUP BY 'id';
Tim N
  • 11
  • 3
  • 1
    _"a subaccount of an account has the same ID, but a null accounttype"_ I don't suppose the real solution is an option for you: fixing this rather obscure design? I would expect there to be a `ParentID` or such instead. Also, there are no `NULL`s in your post... So is a subaccount `NULL` there, or is it the empty string? – underscore_d Apr 19 '18 at 14:24
  • @underscore_d Funniest comment of the day +1. – Tim Biegeleisen Apr 19 '18 at 14:25
  • 1
    This looks a hierarchical query problem, [see here](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query). I won't bang it shut, because you have some other logic in there, but you should look at this link to get started. – Tim Biegeleisen Apr 19 '18 at 14:26
  • 1
    @underscore_d I'm actually in the process of doing a data conversion out of this database. So, yeah, the end solution is definitely fixing the problem. But I'm trying to pull down lists of accounts that have data that can't be programmatically converted for the data entry team to have a roadmap on fixing it. Also, it's NULLs. Sorry, newish to Stack Overflow (and SQL), so still learning. And, no, no ParentID field. They just have the same ID field. – Tim N Apr 19 '18 at 14:28
  • Cool! What about the `NULL` vs empty question? I'm also curious about this: _" I tried an INNER JOIN, but I can't join a column on itself."_ Could you show what you tried and what problem you got? It should be perfectly possible to include the same table as multiple aliases and join the same column in one to that in another. – underscore_d Apr 19 '18 at 14:29
  • Please, [edit] it into the post, rather than leaving it in comments, as it seems directly related to what you already said in the post, and it's useful to show in the question what you tried. Anyway, that'll be because you didn't alias at least one copy of it; you can't have 2 tables with the same name in a query, but you *can* have the same table twice with different aliases. – underscore_d Apr 19 '18 at 14:32
  • @underscore_d You nailed it. Did you want to write an answer and I'll comment with the exact query that worked, so I can give you the answer credit? – Tim N Apr 19 '18 at 14:43
  • @TimN Thanks, but you're the one who figured out the exact query, so it makes more sense if you write an answer and have the credit for yourself, although a hat-tip wouldn't go amiss. :P – underscore_d Apr 19 '18 at 14:45

1 Answers1

1

@underscore_d nailed my problem. I needed to assign aliases to the tables so that I could have the data relate to itself.

SELECT * FROM schema.table AS mbr2 
INNER JOIN schema.table AS mbr1 ON mbr2.id = mbr1.id 
WHERE mbr1.accounttype != 'P' AND 
mbr1.accounttype IS NOT NULL AND 
mbr2.individ != '00' 
GROUP BY mbr1.id;
Tim N
  • 11
  • 3