Ishy Ishy - 4 months ago 14
SQL Question

How can I connect the Primary Key, Foreign Key and Alternative Key all together?

I downloaded this database (it inlcudes the schema): https://wyobiz.wy.gov/business/database.aspx

I want to connect the entire database to basically one table.
So far I have this:

/*Table structure for table `filing` */

DROP TABLE IF EXISTS `filing`;

CREATE TABLE `filing` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`FILING_ID` varchar(200) DEFAULT NULL,
`FILING_TYPE` varchar(200) DEFAULT NULL,
`FILING_SUBTYPE` varchar(200) DEFAULT NULL,
`WORD_DESIGN_TYPE` varchar(200) DEFAULT NULL,
`DURATION_TERM_TYPE` varchar(200) DEFAULT NULL,
`STATUS` varchar(200) DEFAULT NULL,
`SUB_STATUS` varchar(200) DEFAULT NULL,
`STANDING_TAX` varchar(200) DEFAULT NULL,
`STANDING_RA` varchar(200) DEFAULT NULL,
`STANDING_OTHER` varchar(200) DEFAULT NULL,
`PURPOSE` varchar(200) DEFAULT NULL,
`APPLICANT_TYPE` varchar(200) DEFAULT NULL,
`FILING_NUM` varchar(200) DEFAULT NULL,
`FILING_NAME` varchar(200) DEFAULT NULL,
`OLD_NAME` varchar(200) DEFAULT NULL,
`FICTITIOUS_NAME` varchar(200) DEFAULT NULL,
`DOMESTIC_YN` varchar(200) DEFAULT NULL,
`FILING_DATE` varchar(200) DEFAULT NULL,
`DELAYED_EFFECTIVE_DATE` varchar(200) DEFAULT NULL,
`EXPIRATION_DATE` varchar(200) DEFAULT NULL,
`INACTIVE_DATE` varchar(200) DEFAULT NULL,
`RA_RESIGN_CERT_LETTER_DATE` varchar(200) DEFAULT NULL,
`CONVERTED_YN` varchar(200) DEFAULT NULL,
`CONVERTED_FROM` varchar(200) DEFAULT NULL,
`CONVERTED_FROM_NAME` varchar(200) DEFAULT NULL,
`CONVERTED_DATE` varchar(200) DEFAULT NULL,
`ISSUE_ON_RECORD_YN` varchar(200) DEFAULT NULL,
`TRANSFERRED_TO` varchar(200) DEFAULT NULL,
`TRANSFERRED_DATE` varchar(200) DEFAULT NULL,
`FORMATION_LOCALE` varchar(200) DEFAULT NULL,
`CONTINUED_FROM_LOCALE` varchar(200) DEFAULT NULL,
`DOMESTICATED_FROM_LOCALE` varchar(200) DEFAULT NULL,
`FORM_HOME_JURIS_DATE` varchar(200) DEFAULT NULL,
`COMMON_SHARES` varchar(200) DEFAULT NULL,
`COMMON_PAR_VALUE` varchar(200) DEFAULT NULL,
`PREFERRED_SHARES` varchar(200) DEFAULT NULL,
`PREFERRED_PAR_VALUE` varchar(200) DEFAULT NULL,
`ADDITIONAL_STOCK_YN` varchar(200) DEFAULT NULL,
`PRINCIPLE_ADDR1` varchar(200) DEFAULT NULL,
`PRINCIPLE_ADDR2` varchar(200) DEFAULT NULL,
`PRINCIPLE_ADDR3` varchar(200) DEFAULT NULL,
`PRINCIPLE_CITY` varchar(200) DEFAULT NULL,
`PRINCIPLE_STATE` varchar(200) DEFAULT NULL,
`PRINCIPLE_POSTAL_CODE` varchar(200) DEFAULT NULL,
`PRINCIPLE_COUNTRY` varchar(200) DEFAULT NULL,
`MAIL_ADDR1` varchar(200) DEFAULT NULL,
`MAIL_ADDR2` varchar(200) DEFAULT NULL,
`MAIL_ADDR3` varchar(200) DEFAULT NULL,
`MAIL_CITY` varchar(1000) DEFAULT NULL,
`MAIL_STATE` varchar(1000) DEFAULT NULL,
`MAIL_POSTAL_CODE` varchar(1000) DEFAULT NULL,
`MAIL_COUNTRY` varchar(1000) DEFAULT NULL,
`STATE_OF_ORG` varchar(1000) DEFAULT NULL,
`ORG_DATE` varchar(1000) DEFAULT NULL,
`REG_US_OFFICE_YN` varchar(1000) DEFAULT NULL,
`REG_US_DATE` varchar(1000) DEFAULT NULL,
`REG_US_SERIAL_NUM` varchar(1000) DEFAULT NULL,
`REG_US_STATUS` varchar(1000) DEFAULT NULL,
`REG_US_APP_REFUSED_YN` varchar(1000) DEFAULT NULL,
`FIRST_USED_ANYWHERE_DATE` blob,
`FIRST_USED_WYO_DATE` blob,
`AR_EXEMPT_YN` blob,
`TRADEMARK_KEYWORDS` blob,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*Data for the table `filing` */

/*Table structure for table `filing_annual_report` */

DROP TABLE IF EXISTS `filing_annual_report`;

CREATE TABLE `filing_annual_report` (
`FILING_ANNUAL_REPORT_ID` int(11) NOT NULL,
`FILING_ID` int(11) DEFAULT NULL,
`STATUS` varchar(1000) DEFAULT NULL,
`ANNUAL_REPORT_NUM` varchar(1000) DEFAULT NULL,
`FILING_YEAR` varchar(1000) DEFAULT NULL,
`FILING_DATE` varchar(1000) DEFAULT NULL,
`LICENSE_TAX_AMT` varchar(1000) DEFAULT NULL,
PRIMARY KEY (`FILING_ANNUAL_REPORT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `filing_annual_report` */

/*Table structure for table `party` */

DROP TABLE IF EXISTS `party`;

CREATE TABLE `party` (
`PARTY_ID` int(11) NOT NULL,
`PARTY_TYPE` varchar(1000) DEFAULT NULL,
`SOURCE_ID` varchar(1000) DEFAULT NULL,
`SOURCE_TYPE` varchar(1000) DEFAULT NULL,
`ORG_NAME` varchar(1000) DEFAULT NULL,
`FIRST_NAME` varchar(1000) DEFAULT NULL,
`MIDDLE_NAME` varchar(1000) DEFAULT NULL,
`LAST_NAME` varchar(1000) DEFAULT NULL,
`INDIVIDUAL_TITLE` varchar(1000) DEFAULT NULL,
`ADDR1` varchar(1000) DEFAULT NULL,
`ADDR2` varchar(1000) DEFAULT NULL,
`ADDR3` varchar(1000) DEFAULT NULL,
`CITY` varchar(1000) DEFAULT NULL,
`COUNTY` varchar(1000) DEFAULT NULL,
`STATE` varchar(1000) DEFAULT NULL,
`POSTAL_CODE` varchar(1000) DEFAULT NULL,
`COUNTRY` varchar(1000) DEFAULT NULL,
PRIMARY KEY (`PARTY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `party` */

hal hal
Answer

Once you have imported the CSV Data into your DB, the schema seems to indicate PARTY is linked to FILING using the FK PARTY.SOURCE_ID and FILING_ANNUAL_REPORT is linked to FILING using the FK FILING_ANNUAL_REPORT.FILING_ID.

The query to get all entries in one table would be

select * from FILING f
join PARTY p on p.SOURCE_ID=f.FILING_ID
join FILING_ANNUAL_REPORT a on a.FILING_ID=f.FILING_ID

To speed up the query build indexes before running the query:

create index ff on FILING(filing_id);
create index pf on PARTY(source_id);
create index af on FILING_ANNUAL_REPORT(filing_id);