Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to create a view or query in MySQL to obtain if product has one or more product_detail. This are the create sentences for tables:

CREATE TABLE IF NOT EXISTS `stock` (
  `product` INT NOT NULL,
  `kcondition` TINYINT NOT NULL,
  `company` INT(11) NOT NULL,
  `description` BLOB NOT NULL,
  `status` INT(11) NOT NULL,
  `sku` VARCHAR(255) NOT NULL,
  `nweight` INT(11) NOT NULL,
  `nlength` INT(11) NOT NULL,
  `width` DECIMAL(4,4) NOT NULL,
  `height` DECIMAL(4,4) NOT NULL,
  `weight` DECIMAL(4,4) NOT NULL,
  `length` DECIMAL(4,4) NOT NULL,
  `created` TIMESTAMP NULL DEFAULT NULL,
  `modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deletedAt` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`product`, `kcondition`, `company`),
  INDEX `stock_sku_fkey_idx` (`product` ASC),
  INDEX `fk_stock_n_stock_status1_idx` (`status` ASC),
  INDEX `fk_stock_company1_idx` (`company` ASC),
  INDEX `fk_stock_n_condition1_idx` (`kcondition` ASC),
  UNIQUE INDEX `sku_UNIQUE` (`sku` ASC, `product` ASC, `kcondition` ASC, `company` ASC, `status` ASC),
  INDEX `fk_stock_n_weigth_class1_idx` (`nweight` ASC),
  INDEX `fk_stock_n_length_class1_idx` (`nlength` ASC),
  CONSTRAINT `fk_stock_company1`
    FOREIGN KEY (`company`)
    REFERENCES `company` (`id`)
    ON UPDATE CASCADE,
  CONSTRAINT `fk_stock_n_stock_status1`
    FOREIGN KEY (`status`)
    REFERENCES `n_stock_status` (`id`)
    ON UPDATE CASCADE,
  CONSTRAINT `stock_sku_fkey`
    FOREIGN KEY (`product`)
    REFERENCES `product` (`id`)
    ON UPDATE CASCADE,
  CONSTRAINT `fk_stock_n_condition1`
    FOREIGN KEY (`kcondition`)
    REFERENCES `n_condition` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `fk_stock_n_weigth_class1`
    FOREIGN KEY (`nweight`)
    REFERENCES `n_weight` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `fk_stock_n_length_class1`
    FOREIGN KEY (`nlength`)
    REFERENCES `n_length` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `stock_detail` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `product` INT NOT NULL,
  `kcondition` TINYINT NOT NULL,
  `company` INT(11) NOT NULL,
  `upc` VARCHAR(20) NOT NULL,
  `currency` INT(11) NOT NULL,
  `price` DECIMAL(19,4) NOT NULL,
  `amount` INT NOT NULL,
  `availability` INT NOT NULL,
  `created` TIMESTAMP NULL DEFAULT NULL,
  `modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deletedAt` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`, `product`, `kcondition`, `company`, `upc`),
  INDEX `fk_stock_detail_n_unit1_idx` (`currency` ASC),
  CONSTRAINT `fk_stock_detail_stock1`
    FOREIGN KEY (`product` , `kcondition` , `company`)
    REFERENCES `stock` (`product` , `kcondition` , `company`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `fk_stock_detail_n_unit1`
    FOREIGN KEY (`currency`)
    REFERENCES `currency` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `stock_detail_has_product_detail` (
  `stock_detail` INT NOT NULL,
  `product` INT NOT NULL,
  `kcondition` TINYINT NOT NULL,
  `company` INT(11) NOT NULL,
  `product_detail` INT(11) NOT NULL,
  `content` VARCHAR(250) NOT NULL,
  `created` TIMESTAMP NULL DEFAULT NULL,
  `modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deletedAt` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`stock_detail`, `product`, `kcondition`, `company`, `product_detail`),
  INDEX `fk_stock_detail_has_product_detail_product_detail1_idx` (`product_detail` ASC),
  INDEX `fk_stock_detail_has_product_detail_stock_detail1_idx` (`stock_detail` ASC, `product` ASC, `kcondition` ASC, `company` ASC),
  CONSTRAINT `fk_stock_detail_has_product_detail_stock_detail1`
    FOREIGN KEY (`stock_detail` , `product` , `kcondition` , `company`)
    REFERENCES `stock_detail` (`id` , `product` , `kcondition` , `company`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `fk_stock_detail_has_product_detail_product_detail1`
    FOREIGN KEY (`product_detail`)
    REFERENCES `product_detail` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT)
ENGINE = InnoDB;

I made this one:

SELECT s0_.sku AS sku0,
       s0_.description AS description1, 
       s0_.width AS width2, 
       s0_.height AS height3, 
       s0_.weight AS weight4, 
       s0_.length AS length5, 
       s0_.created AS created6, 
       s0_.modified AS modified7, 
       s0_.deletedAt AS deletedAt8, 
       s0_.product AS product9, 
       s0_.company AS company10, 
       s0_.kcondition AS kcondition11, 
       s0_.status AS status12, 
       s0_.nweight AS nweight13, 
       s0_.nlength AS nlength14 
FROM stock s0_ 
INNER JOIN stock_detail s1_ ON s0_.product = s1_.product AND (s1_.deletedAt IS NULL) 
WHERE (s0_.company = 54 AND s0_.status = 1) AND 
      (s0_.deletedAt IS NULL)

But from this point I'm totally lost since I don't know how to get in the same result if each product as one or more product_detail, can any give me a hand on this?

share|improve this question

1 Answer 1

You say you need a view to fetch any product which has one or more product detail, but here I see 3 table definitions, stock, stock_detail and stock_detail_has_product_detail. So are you referring to your product as stock ?

If you mean to get every stock which has at least one row in stock_detail_has_product_detail, then you can try something like this:

select s.* from stock s, stock_has_product_detail sp where sp.product = s.product group by
s.product

You should read the SQL documentation about group by statement.

http://www.w3schools.com/sql/sql_groupby.asp
share|improve this answer
2  
Please don't use the outdated implicit join syntax in the where clause any more. Use explicit JOINs instead. Btw: w3wchool is a horrible place to learn - it's full of errors. And it's definitely not "the SQL documentation". Despite the domain name w3schools has nothing to do with any SQL standards committee. –  a_horse_with_no_name Jan 25 at 9:06
    
I still prefer the old way, from my perspective it looks clearer and cleaner. The w3schools website is indeed not the SQL reference, you should get the official documentation however this link was only to provide a quick meansof understanding how group by statement works. –  schwarz Jan 27 at 18:14
2  
I think the explicit join is cleaner because it clearly separates the JOIN condition from the actual filter condition. And it is less prone to accidental cartesian products and there is no way to do an outer join with the old syntax (except for Oracle and SQL Server) –  a_horse_with_no_name Jan 27 at 18:21
    
Noted, I work mainly with Oracle, that may explain some things. Good point about the separation though. –  schwarz Jan 27 at 20:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.