Advertisement
Guest User

Untitled

a guest
Jun 26th, 2017
86
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 3.98 KB | None | 0 0
  1. When running this in a program like Navicate, it won't let me edit records
  2.  
  3. SELECT
  4. people.lastname,
  5. people.firstname,
  6. address.address,
  7. address.city,
  8. address.state,
  9. address.zip,
  10. positiion.title
  11. FROM
  12. address
  13. INNER JOIN people ON address.idaddress = people.idaddress
  14. INNER JOIN positiion ON positiion.idpositiion = people.idposition
  15.  
  16.  
  17.  
  18. ================================
  19. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  20. SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  21. SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
  22.  
  23. CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
  24. USE `testdb` ;
  25.  
  26. -- -----------------------------------------------------
  27. -- Table `testdb`.`address`
  28. -- -----------------------------------------------------
  29. DROP TABLE IF EXISTS `testdb`.`address` ;
  30.  
  31. CREATE  TABLE IF NOT EXISTS `testdb`.`address` (
  32.  `idaddress` INT NOT NULL AUTO_INCREMENT ,
  33.  `address` VARCHAR(45) NULL ,
  34.  `city` VARCHAR(45) NULL ,
  35.  `state` VARCHAR(45) NULL ,
  36.  `zip` VARCHAR(45) NULL ,
  37.  PRIMARY KEY (`idaddress`) )
  38. ENGINE = InnoDB;
  39.  
  40.  
  41. -- -----------------------------------------------------
  42. -- Table `testdb`.`positiion`
  43. -- -----------------------------------------------------
  44. DROP TABLE IF EXISTS `testdb`.`positiion` ;
  45.  
  46. CREATE  TABLE IF NOT EXISTS `testdb`.`positiion` (
  47.  `idpositiion` INT NOT NULL AUTO_INCREMENT ,
  48.  `title` VARCHAR(45) NULL ,
  49.  PRIMARY KEY (`idpositiion`) )
  50. ENGINE = InnoDB;
  51.  
  52.  
  53. -- -----------------------------------------------------
  54. -- Table `testdb`.`people`
  55. -- -----------------------------------------------------
  56. DROP TABLE IF EXISTS `testdb`.`people` ;
  57.  
  58. CREATE  TABLE IF NOT EXISTS `testdb`.`people` (
  59.  `idpeople` INT NOT NULL AUTO_INCREMENT ,
  60.  `lastname` VARCHAR(45) NULL ,
  61.  `firstname` VARCHAR(45) NULL ,
  62.  `idaddress` INT NULL ,
  63.  `idposition` INT NULL ,
  64.  PRIMARY KEY (`idpeople`) ,
  65.  INDEX `fk_people_address` (`idaddress` ASC) ,
  66.  INDEX `fk_people_positiion1` (`idposition` ASC) ,
  67.  CONSTRAINT `fk_people_address`
  68.    FOREIGN KEY (`idaddress` )
  69.    REFERENCES `testdb`.`address` (`idaddress` )
  70.    ON DELETE NO ACTION
  71.    ON UPDATE NO ACTION,
  72.  CONSTRAINT `fk_people_positiion1`
  73.    FOREIGN KEY (`idposition` )
  74.    REFERENCES `testdb`.`positiion` (`idpositiion` )
  75.    ON DELETE NO ACTION
  76.    ON UPDATE NO ACTION)
  77. ENGINE = InnoDB;
  78.  
  79.  
  80.  
  81. SET SQL_MODE=@OLD_SQL_MODE;
  82. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  83. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  84.  
  85. -- -----------------------------------------------------
  86. -- Data for table `testdb`.`address`
  87. -- -----------------------------------------------------
  88. SET AUTOCOMMIT=0;
  89. USE `testdb`;
  90. INSERT INTO `testdb`.`address` (`idaddress`, `address`, `city`, `state`, `zip`) VALUES ('1', '123 Main St. ', 'Denver', 'CO', '12345');
  91. INSERT INTO `testdb`.`address` (`idaddress`, `address`, `city`, `state`, `zip`) VALUES ('2', '456 Other St.', 'Detroit', 'MI', '54321');
  92.  
  93. COMMIT;
  94.  
  95. -- -----------------------------------------------------
  96. -- Data for table `testdb`.`positiion`
  97. -- -----------------------------------------------------
  98. SET AUTOCOMMIT=0;
  99. USE `testdb`;
  100. INSERT INTO `testdb`.`positiion` (`idpositiion`, `title`) VALUES ('1', 'Manager');
  101. INSERT INTO `testdb`.`positiion` (`idpositiion`, `title`) VALUES ('2', 'IT');
  102. INSERT INTO `testdb`.`positiion` (`idpositiion`, `title`) VALUES ('3', 'Sales');
  103.  
  104. COMMIT;
  105.  
  106. -- -----------------------------------------------------
  107. -- Data for table `testdb`.`people`
  108. -- -----------------------------------------------------
  109. SET AUTOCOMMIT=0;
  110. USE `testdb`;
  111. INSERT INTO `testdb`.`people` (`idpeople`, `lastname`, `firstname`, `idaddress`, `idposition`) VALUES (NULL, 'Doe', 'John', '1', '2');
  112. INSERT INTO `testdb`.`people` (`idpeople`, `lastname`, `firstname`, `idaddress`, `idposition`) VALUES (NULL, 'Smith', 'Joe', '2', '2');
  113. INSERT INTO `testdb`.`people` (`idpeople`, `lastname`, `firstname`, `idaddress`, `idposition`) VALUES (NULL, 'Johnson', 'John', '2', '1');
  114.  
  115. COMMIT;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement