Hello, I don't know if this is the right place to ask anyway I try.
I'm working on a project where I have a schema similar to the one in the picture.
I can't find a way to get the data aggregated as this:
the authenticated user submits a search for "fender stratocaster", the result should be all the fender stratocasters that are currently in the collections of other users that share the same hand and gender as the auth user. Let's say there is only one model named stratocaster, so the program will show that model along with the number of each finish.. example solid X, transparent Y, whatever Z
Let's say there are two or three, I want a derived table that looks like this:
This forum is for Microsoft SQL Server, so you might get answers that won't Work om MySQL.
I belive this will Work in MySQL (but no guarantee):
select gm.id
,gm.model_name
,m.make
,sum(1) as total_collected
,sum(case when f.type='solid 5' then 1 else 0 end as finish_type1
,sum(case when f.type='transparent 7' then 1 else 0 end as finish_type2
,sum(case when f.type='blah' then 1 else 0 end as finish_type3
from guitar_models as gm
inner join makes as as m
on m.id=gm.make_id
inner join collected_guitars as cg
on cg.model_id=gm.id
inner join collected_guitar_user gu
on gu.collected_guitar_id=cg.id
left outer join finish as f
on f.id=cg.finish_id
where gm.model_name='stratocaster'
and m.make='fender'
group by gm.id
,gm.model_name
,m.make
;
hmm, thanks for your reply, however those numbers are not given, they are variables. I mean solid 5 transparent 7 etc,, they depend on how many items are in the collections of other users at a certain time.
You have to be more clear, as I don't know what exactly you're looking for.
Tell you what, if you provide:
- table definitions as create statements
- sample data as insert statements
- expected output (from your provided sample data)
it would be much easier to help.
Oh and by the way, no pictures. We have to be able to copy/paste your statements in order to create test scenario.
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
---------------------------------------------------------------------------------------
| model_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;