josephbupe
Registered User.
- Local time
- Today, 22:14
- Joined
- Jan 31, 2008
- Messages
- 247
Hi,
Just starting with web databases.
I have about six tables which are joined with a junction table called "t_incident_persons" as shown below:
How can I join columns from these tables and run a search with a LIKE statement criteria in multiple fields on form?
The whole table schema is as follows:
I will appreciate your help.
Joseph:banghead:
Just starting with web databases.
I have about six tables which are joined with a junction table called "t_incident_persons" as shown below:
Code:
t_persons
+-----------+-----------+----------+----------+
| PersonID | FamilyName| FirstName| CountryID|
+-----------+-----------+----------+----------+
| 1 | Bupe | Joseph | 243 |
| 2 | Kansi | Brown | 243 |
| 3 | Luma | Henry | 243 |
+-----------+-----------+----------+----------+
t_incidents
+--------------+-------------------+-------------+----------------+--------+--------+
| IncidentID | IncidentCountryID |IncidentDate |OffenceKeywordID|AgencyID|StatusID|
+--------------+-------------------+-------------+----------------+--------+--------+
| 1 | 243 |12/03/2003 |1 |6 |2 |
| 2 | 243 |15/05/2004 |1 |6 |2 |
| 3 | 243 |16/12/2007 |3 |6 |3 |
+--------------+-------------------+-------------+----------------+--------+--------+
t_incident_persons
+-----------+----------+
| IncidentID| PersonID |
+-----------+----------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
+-----------+----------+
The whole table schema is as follows:
Code:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `mydb`;
-- -----------------------------------------------------
-- Table `mydb`.`t_countries`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t_countries` (
`CountryID` INT NOT NULL,
`Country` VARCHAR(60) NULL,
`OfficialName` VARCHAR(80) NULL,
`ISO3166_Alpha2` VARCHAR(2) NULL,
`ISO3166_Alpha3` VARCHAR(3) NULL,
`ISO3166_Numeric` VARCHAR(3) NULL,
`Capital` VARCHAR(60) NULL,
`Currency` VARCHAR(30) NULL,
`CurrencyCode` VARCHAR(5) NULL,
`CallingCode` VARCHAR(5) NULL,
`Internet` VARCHAR(255) NULL,
`AreaSqKm` DOUBLE NULL,
`AreaSqMi` DOUBLE NULL,
PRIMARY KEY (`CountryID`, `Currency`, `CurrencyCode`, `CallingCode`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`t_person_status`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t_person_status` (
`StatusID` INT NOT NULL,
`Status` VARCHAR(45) NULL,
PRIMARY KEY (`StatusID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`t_persons`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t_persons` (
`PersonID` INT NOT NULL,
`FamilyName` VARCHAR(45) NULL,
`FirstName` VARCHAR(45) NULL,
`OtherNames` VARCHAR(45) NULL,
`Gender` VARCHAR(45) NULL,
`DateOfBirth` DATE NULL,
`PlaceOfBirth` VARCHAR(45) NULL,
`Village` VARCHAR(45) NULL,
`Chief` VARCHAR(45) NULL,
`District` VARCHAR(45) NULL,
`ImagePath` VARCHAR(255) NULL,
`PassportNo` VARCHAR(45) NULL,
`NRCNo` VARCHAR(45) NULL,
`CountryID` INT NOT NULL,
`StatusID` INT NOT NULL,
PRIMARY KEY (`PersonID`, `CountryID`, `StatusID`),
INDEX `fk_t_persons_t_countries1_idx` (`CountryID` ASC),
INDEX `fk_t_persons_t_person_status1_idx` (`StatusID` ASC),
CONSTRAINT `fk_t_persons_t_countries1`
FOREIGN KEY (`CountryID`)
REFERENCES `mydb`.`t_countries` (`CountryID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_t_persons_t_person_status1`
FOREIGN KEY (`StatusID`)
REFERENCES `mydb`.`t_person_status` (`StatusID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`t_agencies`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t_agencies` (
`AgencyID` INT NOT NULL,
`Agency` VARCHAR(255) NULL,
PRIMARY KEY (`AgencyID`),
UNIQUE INDEX `AgencyID_UNIQUE` (`AgencyID` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`t_incidents`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t_incidents` (
`IncidentID` INT NOT NULL,
`IncidentCountryID` INT NOT NULL,
`AgencyID` INT NOT NULL,
`PersonID` INT NULL,
`IncidentDate` DATE NULL,
`Description` TEXT NULL,
`NCBRef` VARCHAR(45) NULL,
`Ref` VARCHAR(45) NULL,
PRIMARY KEY (`IncidentID`, `IncidentCountryID`, `AgencyID`),
UNIQUE INDEX `IncidentID_UNIQUE` (`IncidentID` ASC),
INDEX `fk_t_incidents_t_agencies1_idx` (`AgencyID` ASC),
INDEX `fk_t_incidents_t_countries1_idx` (`IncidentCountryID` ASC),
CONSTRAINT `fk_t_incidents_t_agencies1`
FOREIGN KEY (`AgencyID`)
REFERENCES `mydb`.`t_agencies` (`AgencyID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_t_incidents_t_countries1`
FOREIGN KEY (`IncidentCountryID`)
REFERENCES `mydb`.`t_countries` (`CountryID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`t_offencekeywords`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t_offencekeywords` (
`KeywordID` INT NOT NULL,
`Keyword` VARCHAR(255) NULL,
PRIMARY KEY (`KeywordID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`t_incident_persons`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t_incident_persons` (
`IncidentPersonsID` INT NOT NULL,
`PersonID` INT NOT NULL,
`KeywordID` INT NOT NULL,
`IncidentID` INT NOT NULL,
PRIMARY KEY (`IncidentPersonsID`, `PersonID`, `KeywordID`, `IncidentID`),
INDEX `fk_t_incident_persons_t_persons_idx` (`PersonID` ASC),
INDEX `fk_t_incident_persons_t_incidents1_idx` (`IncidentID` ASC),
INDEX `fk_t_incident_persons_t_offencekeywords1_idx` (`KeywordID` ASC),
CONSTRAINT `fk_t_incident_persons_t_persons`
FOREIGN KEY (`PersonID`)
REFERENCES `mydb`.`t_persons` (`PersonID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_t_incident_persons_t_incidents1`
FOREIGN KEY (`IncidentID`)
REFERENCES `mydb`.`t_incidents` (`IncidentID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_t_incident_persons_t_offencekeywords1`
FOREIGN KEY (`KeywordID`)
REFERENCES `mydb`.`t_offencekeywords` (`KeywordID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`t_syndicates`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t_syndicates` (
`SyndicateID` INT NOT NULL,
`Syndicate` VARCHAR(255) NULL,
PRIMARY KEY (`SyndicateID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`t_person_syndicate`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t_person_syndicate` (
`PersonSyndicateID` INT NOT NULL,
`SyndicateID` INT NOT NULL,
`PersonID` INT NOT NULL,
PRIMARY KEY (`PersonSyndicateID`, `SyndicateID`, `PersonID`),
INDEX `fk_t_person_syndicate_t_syndicates1_idx` (`SyndicateID` ASC),
INDEX `fk_t_person_syndicate_t_persons1_idx` (`PersonID` ASC),
CONSTRAINT `fk_t_person_syndicate_t_syndicates1`
FOREIGN KEY (`SyndicateID`)
REFERENCES `mydb`.`t_syndicates` (`SyndicateID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_t_person_syndicate_t_persons1`
FOREIGN KEY (`PersonID`)
REFERENCES `mydb`.`t_persons` (`PersonID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Joseph:banghead: