Go Back   Access World Forums > Apps and Windows > Web Design and Development > PHP & MySQL

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-01-2014, 02:19 AM   #1
josephbupe
Newly Registered User
 
Join Date: Jan 2008
Posts: 225
Thanks: 33
Thanked 1 Time in 1 Post
josephbupe is on a distinguished road
Select multiple columns from two tables for searching

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:

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        |
+-----------+----------+
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:


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;
I will appreciate your help.

Joseph

josephbupe is offline   Reply With Quote
Reply

Tags
mysql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Efficiently searching multiple tables NZArchie Tables 7 08-07-2011 10:05 PM
Searching multiple columns for partial text dkinnz Modules & VBA 5 02-23-2007 07:06 AM
[SOLVED] Searching across multiple tables with same columns.... ds9686 General 1 12-02-2005 03:37 PM
Searching multiple tables Michelangelo Queries 3 01-10-2003 05:50 AM
Searching/Updating multiple tables at once vangogh228 Queries 2 11-12-2002 08:02 AM




All times are GMT -8. The time now is 10:05 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World