-- -- Table structure for table `vbi_company` -- DROP TABLE IF EXISTS `vbi_company`; CREATE TABLE `vbi_company` ( `Company_ID` varchar(20) NOT NULL default '', `Company_Name` varchar(60) NOT NULL default '', `Company_Notes` text NOT NULL, `Company_URL` varchar(60) NOT NULL default '', PRIMARY KEY (`Company_ID`), UNIQUE KEY `IDX_Company_Name` (`Company_Name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=1; -- -- Dumping data for table `vbi_company` -- INSERT INTO `vbi_company` VALUES ('1758-6676','Tanya','notes go here','http://www.richweb.com'); INSERT INTO `vbi_company` VALUES ('1958-4982','1958-4982','',''); INSERT INTO `vbi_company` VALUES ('2753-8293','test','ffff',''); INSERT INTO `vbi_company` VALUES ('2776-4339','2776-4339','',''); INSERT INTO `vbi_company` VALUES ('4157-8941','4157-8941','',''); INSERT INTO `vbi_company` VALUES ('4862-4465','test factor','afsdaf',''); INSERT INTO `vbi_company` VALUES ('7147-6846','7147-6846','',''); INSERT INTO `vbi_company` VALUES ('7621-1597','7621-1597','',''); INSERT INTO `vbi_company` VALUES ('7833-4732','7833-4732','',''); INSERT INTO `vbi_company` VALUES ('9311-1414','9311-1414','',''); INSERT INTO `vbi_company` VALUES ('9414-8367','9414-8367','',''); INSERT INTO `vbi_company` VALUES ('aig1','AIG Annuity Insurance','','http://www.aiannuityaccess.com'); INSERT INTO `vbi_company` VALUES ('AN','American National Life Insurance Company','','http://www.imo.anicoweb.com/'); INSERT INTO `vbi_company` VALUES ('bli','Banner Life Insurance Company','','http://www.bannerlife.com'); INSERT INTO `vbi_company` VALUES ('dog_food','Dog Food','','http://www.richweb.com'); INSERT INTO `vbi_company` VALUES ('EG','Empire General','',''); INSERT INTO `vbi_company` VALUES ('Ge','Genworth Financial','',''); INSERT INTO `vbi_company` VALUES ('ING','ING','',''); -- -- Table structure for table `vbi_ic_prod_list` -- DROP TABLE IF EXISTS `vbi_ic_prod_list`; CREATE TABLE `vbi_ic_prod_list` ( `Prod_ID` varchar(20) NOT NULL default '', `Company_ID` varchar(20) NOT NULL default '', `Prod_Desc` text NOT NULL, `Enable_Flag` varchar(1) NOT NULL default '', `Variable_Annuity` char(1) NOT NULL default '', `Fixed_Annuity` char(1) NOT NULL default '', `Life_Insurance` char(1) NOT NULL default '', `Long_Term_Care` char(1) NOT NULL default '', `Variable_Life_Insurance` char(1) NOT NULL default '', `Prod_Rank` int(2) NOT NULL default '10', PRIMARY KEY (`Prod_ID`), KEY `Company_ID` (`Company_ID`), CONSTRAINT `vbi_ic_prod_list_ibfk_1` FOREIGN KEY (`Company_ID`) REFERENCES `vbi_company` (`Company_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=1; -- -- Dumping data for table `vbi_ic_prod_list` -- INSERT INTO `vbi_ic_prod_list` VALUES ('1','aig1','Life Insurance, Long Term Care, Variable Life:
\r\nInt. Wholesaler: 800-220-6049 - Jim Gilbert ext.\r\n212\r\n
\r\n','Y','','','Y','','N',10); INSERT INTO `vbi_ic_prod_list` VALUES ('1368-6126','1758-6676','Its a fun place to be.','Y','Y','','N','','',10); INSERT INTO `vbi_ic_prod_list` VALUES ('1393-1174','4862-4465','werwerwer','Y','Y','','Y','','',10); INSERT INTO `vbi_ic_prod_list` VALUES ('2','aig1','Fixed & Variable\r\nAnnuities:
\r\nInt. Wholesaler: 888-237-4210
\r\nExternal\r\nWholesaler: 800-630-2262 - John Anderson\r\nGeneral Support:\r\n800-424-4990','Y','','Y','','','',1); INSERT INTO `vbi_ic_prod_list` VALUES ('3','EG','Fixed & Variable\r\nAnnuities:
\r\nInt. Wholesaler: 800-833-4360 - press\r\n2
\r\nExternal Wholesaler: 800-833-4360 - Doug Culp\r\nGeneral Support:\r\n800-424-4990','Y','','Y','','','',10); INSERT INTO `vbi_ic_prod_list` VALUES ('3134-2445','aig1','sd','N','','','','','',5); INSERT INTO `vbi_ic_prod_list` VALUES ('4','bli','Life Insurance, Long Term Care, Variable Life:
\r\nInt. Wholesaler:\r\n800-220-6049 - Jim Gilbert Ext. 212
\r\n\r\nEd Meakim Ext. 225
\r\n Larry Anmuth \r\nExt. 217 LTC\r\n\r\n
\r\n','Y','','','Y','Y','N',10); INSERT INTO `vbi_ic_prod_list` VALUES ('5','ING','Life Insurance, Long Term Care, Variable Life:
\r\nInt. Wholesaler:\r\n800-220-6049 - Jim Gilbert Ext. 212
\r\n\r\n','Y','','','Y','','',10); INSERT INTO `vbi_ic_prod_list` VALUES ('5287-1182','1758-6676','Great Place\r\n4235 Innslake Drive\r\nGlen Allen, VA 23060\r\n804.747.8592\r\nAttention: Tanya Noble-Marx','Y','N','Y','','','Y',10); INSERT INTO `vbi_ic_prod_list` VALUES ('5646-9463','2753-8293','test','N','','Y','','','',10); INSERT INTO `vbi_ic_prod_list` VALUES ('6','AN','Fixed &\r\nVariable Annuities:
\r\nInt. Wholesaler:\r\n888-000-0000
\r\nExternal Wholesaler: 800-000-0000- \r\n\r\nGeneral Support: \r\n800-000-0000','Y','Y','Y','','','',10); INSERT INTO `vbi_ic_prod_list` VALUES ('7','Ge','Life Insurance, Long Term Care, Variable Life:
\r\nInt. Wholesaler: 800-220-6049 - Jim Gilbert Ext.\r\n212\r\n Ed Meakim Ext. 225\r\n\r\n','Y','','','Y','','Y',10); -- create view drop view if exists test_view_product_company; CREATE VIEW test_view_product_company AS select vbi_ic_prod_list.Prod_ID, vbi_ic_prod_list.Company_ID, vbi_company.Company_Name from vbi_ic_prod_list left join vbi_company on vbi_ic_prod_list.Company_ID = vbi_company.Company_ID where vbi_ic_prod_list.Company_ID != ''; -- test query select Company_Name from test_view_product_company order by Company_Name;