The table structure used to be documented in the Administrator guide, but it now only contains a list of tables. There is a KB article that details them here: Knowledge Base Articles Unfortunately it lacks an entity relationship diagram to show which tables join to which tables.
You need to be careful with package groups, as unbound package groups can span multiple states, and as I said previously, a package can belong to multiple unbound package groups.
To get what you want, you need to start with the packages. Each package is associated with one (and only one) state. You can therefore get all the packages in a state quite easily by pinning down the STATEOBJID in the HARPACKAGE table. You probably won't want to use ids, so you'll also need to use HARSTATE and HARENVIRONMENT to lookup the state and project respectively. I gave you the other tables you need in the other two queries. I'm not quite sure what you need, but the following query will print every package name and package group name combination for all the packages in a state (I'll let you figure out the GROUP BY etc).
SELECT p.packagename, g.pkggrpname
FROM harpackage p, harpackagegroup g, harpkgsinpkggrp pig,
harstate s, harenvironment e
WHERE p.stateobjid=s.stateobjid AND s.envobjid=e.envobjid
AND g.pkggrpobjid=pig.pkggrpobjid AND pig.packageobjid=p.packageobjid
AND s.statename='MyState' AND e.environmentname='MyProject'
Best regards,
Robert