This is familiar - I am finishing ServiceDesk object Visualizer so I use all these SQLs (though I have them generated automatically from SD's object model).
So here's few snippets that can help (I hope you will be able to add appropriate WHERE to filter on Category):
(Configuration Item-Child Configuration Items-Configuration Item): SELECT cic_citb_oid, cic_cita_oid FROM itsm_cicomponents
(Configuration Item-Parent CIs-Configuration Item): SELECT cic_cita_oid, cic_citb_oid FROM itsm_cicomponents
These will give you all parent-child or child-parent pairs. So if you filter on first attribute to be your object and do join with Category on second object and filter to required categories that should be it. If you do not know how to join on Category the easiest way would be having your SD create reporting views in database and look up their DDL to see how to get Category into view.
CI Relations have a reference to Relation Type (i.e. something like Connected to, Installed on, Backup of, ...). It is an ttribute called cir_rty_oid. As your select is right now you have no way of distinguishing what kind of relations these CIs have.
But now you are treating possibly different kind of relations as parent/child which an be very misleading (as usually there are several kinds of relations defined and used in default SD installation). Open some kind of CI in client and you will see 3 tabs: Parent CIs, Child CIs and Related CIs. Parent/Child is usually used as a way of (de)composition - i.e. something (Child) is part of something else (Parent). Relations have a Type which is a sort of Code field...
Well as we are in the middle of a candidate process selection and I have my full name in this forum I rather not give any detailed info ;)
What I can say that we have looked at and will continue looking at
Remedy HP Service Center SupportWorks (Hornbill)
Also had a look at Prolin Smart Client 7 to replace HPOVSD 4.5 GUI, but to be honest it's far from Production quality yet. But looking forward to seeing new versions from them :) Might be a good alternative until we move on to another product (which can take years)
Regarding Remedy and Service Manager: in case you're accustomed to be able to do various customization tasks in SD I advise trying to do similar in any of these (or having the vendor/integrator show some of it on your demand). You will find that some seemingly easy tasks require a lot of programming work. Both of these platforms require skilled developers and reversers to be able to decipher existing functionality, be able to design required functionality, assess (and counteract) impact to various (seemingly unrelated) areas of the product. Also do not forget to have these guys handy in case you want to upgrade the platform - you will usually need to redo all the changes from scratch as you need to re-analyse changed stuff, re-design it, re-assess the impact, ... I hope you get the idea...
Right now as mentioned we are only in a candidate selection stage, the real challenge/headache comes when we delve into the functionality of the selected candidates products and how they fit into our integrations.
I am sure we are in for some big surprises! My company is definitely not one that can make use of "out of the box" software.
Thankfully we have great internal expertise in almost all areas, so we seldom have to rely on costly external consultants to "hold our hand" when dealing with such solutions.
PS: The above list of candidates are not final, and may be subject to change and additions :) (trying to be unbiased here, hehe)