MySQL Forums
Forum List  »  General

Re: Help with derived table from many to many relationship
Posted by: Christian Nuvoli
Date: March 21, 2016 10:53AM

here's the sql to recreate a similar schema:

what I want to get from this is:

user request info for an item_model

result should be

all the item_models owned (owned_items) by users with same "mod_usr_attr" and "attr1" as the requesting user (authenticated users).
Then the data should be aggregated as
---------------------------------------------------------------------------------------
| owned_item | mod_usr_attr(case1)| mod_usr_attr(case2)|...|mod_usr_attr(caseN)|total |
---------------------------------------------------------------------------------------
| model_id   | number of items    | number of items    |...| number of items   |number|
| ...        | ...                | ...                |...| ...               | ...  |


-- MySQL Script generated by MySQL Workbench
-- Mon Mar 21 17:29:29 2016
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

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

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`makes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`makes` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `make` VARCHAR(255) CHARACTER SET 'utf8' NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `makes_make_unique` (`make` ASC))
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`model_attr`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`model_attr` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `case1` INT(10) UNSIGNED NULL DEFAULT NULL,
  `case2` DECIMAL(4,2) NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`item_models`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`item_models` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `make_id` INT(10) UNSIGNED NULL DEFAULT NULL,
  `model_name` VARCHAR(45) CHARACTER SET 'utf8' NULL DEFAULT NULL,
  `model_attr_id` INT(10) UNSIGNED NULL DEFAULT NULL,
  `price` INT(10) UNSIGNED NULL DEFAULT NULL,
  `currency` VARCHAR(3) CHARACTER SET 'utf8' NULL DEFAULT NULL,
  `description` TEXT CHARACTER SET 'utf8' NULL DEFAULT NULL,
  `picture` VARCHAR(255) CHARACTER SET 'utf8' NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `item_models_make_id_foreign` (`make_id` ASC),
  INDEX `item_models_model_attr_id_foreign` (`model_attr_id` ASC),
  INDEX `model_name` (`model_name` ASC),
  CONSTRAINT `item_models_make_id_foreign`
    FOREIGN KEY (`make_id`)
    REFERENCES `mydb`.`makes` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `item_models_model_attr_id_foreign`
    FOREIGN KEY (`model_attr_id`)
    REFERENCES `mydb`.`model_attr` (`id`)
    ON DELETE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`mod_usr_attr`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`mod_usr_attr` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `case1` DECIMAL(4,2) NULL DEFAULT NULL,
  `case2` DECIMAL(4,2) NULL DEFAULT NULL,
  `case3` DECIMAL(4,2) NULL DEFAULT NULL,
  `case4` DECIMAL(4,2) NULL DEFAULT NULL,
  `case4` DECIMAL(4,2) NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`owned_items`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`owned_items` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `model_id` INT(10) UNSIGNED NOT NULL,
  `mod_usr_attr_id` INT(10) UNSIGNED NOT NULL,
  `make_id` INT(10) UNSIGNED NOT NULL,
  `model_attr` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `owned_items_model_id_foreign` (`model_id` ASC),
  INDEX `owned_items_mod_usr_attr_id_foreign` (`mod_usr_attr_id` ASC),
  INDEX `owned_items_make_id_foreign` (`make_id` ASC),
  INDEX `owned_items_model_attr_id_foreign` (`model_attr` ASC),
  CONSTRAINT `owned_items_make_id_foreign`
    FOREIGN KEY (`make_id`)
    REFERENCES `mydb`.`makes` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `owned_items_model_attr_id_foreign`
    FOREIGN KEY (`model_attr`)
    REFERENCES `mydb`.`model_attr` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `owned_items_model_id_foreign`
    FOREIGN KEY (`model_id`)
    REFERENCES `mydb`.`item_models` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `owned_items_mod_usr_attr_id_foreign`
    FOREIGN KEY (`mod_usr_attr_id`)
    REFERENCES `mydb`.`mod_usr_attr` (`id`)
    ON DELETE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`usr_attr1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`usr_attr1` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(45) CHARACTER SET 'utf8' NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`genders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`genders` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `sex` VARCHAR(45) CHARACTER SET 'utf8' NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`users` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) CHARACTER SET 'utf8' NOT NULL,
  `email` VARCHAR(255) CHARACTER SET 'utf8' NOT NULL,
  `password` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL,
  `attr1_id` INT(10) UNSIGNED NULL DEFAULT NULL,
  `mod_usr_attr_id` INT(10) UNSIGNED NULL DEFAULT NULL,
  `gender_id` INT(10) UNSIGNED NULL DEFAULT NULL,
  `user_pic` VARCHAR(255) CHARACTER SET 'utf8' NULL DEFAULT NULL,
  `remember_token` VARCHAR(100) CHARACTER SET 'utf8' NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `users_username_unique` (`username` ASC),
  UNIQUE INDEX `users_email_unique` (`email` ASC),
  INDEX `users_usr_attr1_id_foreign` (`attr1_id` ASC),
  INDEX `users_mod_usr_id_foreign` (`mod_usr_attr_id` ASC),
  INDEX `users_gender_id_foreign` (`gender_id` ASC),
  CONSTRAINT `users_usr_attr1_id_foreign`
    FOREIGN KEY (`attr1_id`)
    REFERENCES `mydb`.`usr_attr1` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `users_gender_id_foreign`
    FOREIGN KEY (`gender_id`)
    REFERENCES `mydb`.`genders` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `users_mod_usr_id_foreign`
    FOREIGN KEY (`mod_usr_attr_id`)
    REFERENCES `mydb`.`mod_usr_attr` (`id`)
    ON DELETE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`owned_item_user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`owned_item_user` (
  `user_id` INT(10) UNSIGNED NOT NULL,
  `owned_item_id` INT(10) UNSIGNED NOT NULL,
  INDEX `owned_item_user_user_id_index` (`user_id` ASC),
  INDEX `owned_item_user_owned_item_id_index` (`owned_item_id` ASC),
  CONSTRAINT `owned_item_user_collected_items_id_foreign`
    FOREIGN KEY (`owned_item_id`)
    REFERENCES `mydb`.`owned_items` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `owned_item_user_user_if_foreign`
    FOREIGN KEY (`user_id`)
    REFERENCES `mydb`.`users` (`id`)
    ON DELETE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;


Options: ReplyQuote


Subject
Written By
Posted
Re: Help with derived table from many to many relationship
March 21, 2016 10:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.