SELECT category_name, category_id
FROM jos_vm_category
WHERE category_id
IN (

SELECT category_parent_id
FROM jos_vm_category_xref
WHERE category_parent_id
)

CREATE TABLE IF NOT EXISTS `jos_vm_category` (
`category_id` int(11) NOT NULL auto_increment,
`vendor_id` int(11) NOT NULL default '0',
`category_name` varchar(128) NOT NULL default '',
`category_description` text,
`category_thumb_image` varchar(255) default NULL,
`category_full_image` varchar(255) default NULL,
`category_publish` char(1) default NULL,
`cdate` int(11) default NULL,
`mdate` int(11) default NULL,
`category_browsepage` varchar(255) NOT NULL default 'browse_1',
`products_per_row` tinyint(2) NOT NULL default '1',
`category_flypage` varchar(255) default NULL,
`list_order` int(11) default NULL,
PRIMARY KEY (`category_id`),
KEY `idx_category_vendor_id` (`vendor_id`),
KEY `idx_category_name` (`category_name`)
) TYPE=MyISAM COMMENT='Product Categories are stored here';

CREATE TABLE IF NOT EXISTS `jos_vm_category_xref` (
`category_parent_id` int(11) NOT NULL default '0',
`category_child_id` int(11) NOT NULL default '0',
`category_list` int(11) default NULL,
KEY `category_xref_category_parent_id` (`category_parent_id`),
KEY `category_xref_category_child_id` (`category_child_id`),
KEY `idx_category_xref_category_list` (`category_list`)
) TYPE=MyISAM COMMENT='Category child-parent relation list';