Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- When running this in a program like Navicate, it won't let me edit records
- SELECT
- people.lastname,
- people.firstname,
- address.address,
- address.city,
- address.state,
- address.zip,
- positiion.title
- FROM
- address
- INNER JOIN people ON address.idaddress = people.idaddress
- INNER JOIN positiion ON positiion.idpositiion = people.idposition
- ================================
- 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';
- CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
- USE `testdb` ;
- -- -----------------------------------------------------
- -- Table `testdb`.`address`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `testdb`.`address` ;
- CREATE TABLE IF NOT EXISTS `testdb`.`address` (
- `idaddress` INT NOT NULL AUTO_INCREMENT ,
- `address` VARCHAR(45) NULL ,
- `city` VARCHAR(45) NULL ,
- `state` VARCHAR(45) NULL ,
- `zip` VARCHAR(45) NULL ,
- PRIMARY KEY (`idaddress`) )
- ENGINE = InnoDB;
- -- -----------------------------------------------------
- -- Table `testdb`.`positiion`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `testdb`.`positiion` ;
- CREATE TABLE IF NOT EXISTS `testdb`.`positiion` (
- `idpositiion` INT NOT NULL AUTO_INCREMENT ,
- `title` VARCHAR(45) NULL ,
- PRIMARY KEY (`idpositiion`) )
- ENGINE = InnoDB;
- -- -----------------------------------------------------
- -- Table `testdb`.`people`
- -- -----------------------------------------------------
- DROP TABLE IF EXISTS `testdb`.`people` ;
- CREATE TABLE IF NOT EXISTS `testdb`.`people` (
- `idpeople` INT NOT NULL AUTO_INCREMENT ,
- `lastname` VARCHAR(45) NULL ,
- `firstname` VARCHAR(45) NULL ,
- `idaddress` INT NULL ,
- `idposition` INT NULL ,
- PRIMARY KEY (`idpeople`) ,
- INDEX `fk_people_address` (`idaddress` ASC) ,
- INDEX `fk_people_positiion1` (`idposition` ASC) ,
- CONSTRAINT `fk_people_address`
- FOREIGN KEY (`idaddress` )
- REFERENCES `testdb`.`address` (`idaddress` )
- ON DELETE NO ACTION
- ON UPDATE NO ACTION,
- CONSTRAINT `fk_people_positiion1`
- FOREIGN KEY (`idposition` )
- REFERENCES `testdb`.`positiion` (`idpositiion` )
- 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;
- -- -----------------------------------------------------
- -- Data for table `testdb`.`address`
- -- -----------------------------------------------------
- SET AUTOCOMMIT=0;
- USE `testdb`;
- INSERT INTO `testdb`.`address` (`idaddress`, `address`, `city`, `state`, `zip`) VALUES ('1', '123 Main St. ', 'Denver', 'CO', '12345');
- INSERT INTO `testdb`.`address` (`idaddress`, `address`, `city`, `state`, `zip`) VALUES ('2', '456 Other St.', 'Detroit', 'MI', '54321');
- COMMIT;
- -- -----------------------------------------------------
- -- Data for table `testdb`.`positiion`
- -- -----------------------------------------------------
- SET AUTOCOMMIT=0;
- USE `testdb`;
- INSERT INTO `testdb`.`positiion` (`idpositiion`, `title`) VALUES ('1', 'Manager');
- INSERT INTO `testdb`.`positiion` (`idpositiion`, `title`) VALUES ('2', 'IT');
- INSERT INTO `testdb`.`positiion` (`idpositiion`, `title`) VALUES ('3', 'Sales');
- COMMIT;
- -- -----------------------------------------------------
- -- Data for table `testdb`.`people`
- -- -----------------------------------------------------
- SET AUTOCOMMIT=0;
- USE `testdb`;
- INSERT INTO `testdb`.`people` (`idpeople`, `lastname`, `firstname`, `idaddress`, `idposition`) VALUES (NULL, 'Doe', 'John', '1', '2');
- INSERT INTO `testdb`.`people` (`idpeople`, `lastname`, `firstname`, `idaddress`, `idposition`) VALUES (NULL, 'Smith', 'Joe', '2', '2');
- INSERT INTO `testdb`.`people` (`idpeople`, `lastname`, `firstname`, `idaddress`, `idposition`) VALUES (NULL, 'Johnson', 'John', '2', '1');
- COMMIT;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement