Here is what you're looking for.
CMS_Tree is the table which holds the content tree (node) structure. The fields in this table remain as a constant so when you fill out webparts or document queries, you can use those constant values no matter what language is being used. For instance, NodeAliasPath remains the same value no matter what language is entered in the CMS_Document table.
CMS_Document is the table where all the "pages" for a tree node are stored. CMS_Document is related to the CMS_Tree table via the DocumentNodeID field. When joining to the page type table(s), you will use the DocumentForeignKeyValue, which will be the PK value in the page type table you are joining to.
CMS_View_Tree_Joined is the view which joins both the CMS_Tree and CMS_Document table together. This is very helpful to know and use. Keep in mind you'll need to join other tables to it if you wish to get more coupled data.
If you want to use Categories for page types, I suggest following the documentation and using the out of the box configuration. Keep in mind, the table CMS_DocumentCategory only has the DocumentID and CategoryID fields in it. Remember cultures are only stored with the document. So a page in every language can have their own categories assigned to them.
So your query would look something like:
SELECT v.DocumentName, v.DocumentID, v.NodeAliasPath, c.CategoryDisplayName
FROM View_CMS_Tree_Joined AS v
LEFT OUTER JOIN CMS_DocumentCategory AS dc ON v.DocumentID = dc.DocumentID
INNER JOIN CMS_Category c on dc.CategoryID = c.CategoryID
WHERE v.ClassName = 'cms.menuitem'
AND v.DocumentCulture = 'en-us'
AND c.CategoryName = 'CategoryCodename'
Or if you're using a DocumentQuery you can use something like:
var docs = DocumentHelper.GetDocuments("cms.menuitem").WhereEquals("DocumentCulture", "en-us").OnCurrentSite().InCategories("CategoryCodeName");