Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2014
    Posts
    7

    Unanswered: Need to query a self referencing table to find all items that are NOT the parent of a

    Like the title says, I need to query a self referencing table to find all items that are NOT the parent of any item.


    Here is my table:


    itemNumber | quantity | cost | parentItem | level
    ------------|----------|------|------------|-------
    1 | 1 | 31 | NULL | 0
    2 | 2 | 4 | 1 | 1
    3 | 1 | 2 | 2 | 2
    4 | 2 | 1 | 3 | 3
    5 | 1 | 2 | 2 | 2
    6 | 1 | 10 | 1 | 1
    7 | 2 | 5 | 6 | 2
    8 | 1 | 1 | 7 | 3
    9 | 1 | 3 | 7 | 3
    10 | 1 | 1 | 7 | 3
    11 | 1 | 6 | 1 | 1
    12 | 3 | 2 | 11 | 2
    13 | 1 | 7 | 1 | 1

    Here is my schema:

    CREATE TABLE IF NOT EXISTS `recursivebom`.`level` (
    `level` INT NOT NULL,
    PRIMARY KEY (`level`))
    ENGINE = InnoDB;


    -- -----------------------------------------------------
    -- Table `recursivebom`.`item`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `recursivebom`.`item` (
    `itemNumber` INT NOT NULL,
    `quantity` DECIMAL NULL,
    `cost` DECIMAL NULL,
    `parentItem` INT NULL,
    `level` INT NOT NULL,
    PRIMARY KEY (`itemNumber`, `level`),
    INDEX `fk_item_item1_idx` (`parentItem` ASC),
    INDEX `fk_item_level1_idx` (`level` ASC),
    CONSTRAINT `fk_item_item1`
    FOREIGN KEY (`parentItem`)
    REFERENCES `recursivebom`.`item` (`itemNumber`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_item_level1`
    FOREIGN KEY (`level`)
    REFERENCES `recursivebom`.`level` (`level`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so presumably thats row that doesn't appear in parentitem
    so use a left join to return all item numbers and any mnatching parentitems
    in the where clause reject all itemnumbers in the resultset if the parentitem is NOT null

    ..that should be soemthign like:-
    Code:
    select t1.itemnumber from mytable as t1
    left join mytable as t2 on t1.itemnumber = t2.parentitem
    where t2.parentitem is null
    change mytable for whatever your table is called
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2014
    Posts
    7
    This worked, thanks so much healdem!

    Now I have another question, what would I have to do to add up the product of cost and quantity of all the nonparent items.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    new question new thread please.. dats dem dare rules
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use the sum function
    add a group by clause
    replace the where with a having clause. you cant use where with group by
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2014
    Posts
    7
    Thanks again!

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by healdem View Post
    replace the where with a having clause. you cant use where with group by
    You actually can use a WHERE clause with a GROUP BY in standard SQL, although I haven't explicitly tested that in MySQL. In this case, I think that using WHERE with GROUP BY is exactly what you ought to do.

    The WHERE clause is executed as rows are added to the candidate row set. The HAVING clause is executed after the GROUP BY is performed. This perform the same logical function, but at two distinctly different points in the process.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jun 2014
    Posts
    7
    Thanks for the tip Pat!

    I'm a noob and I keep running into unforeseen problems.

    I was able to generate a query that rolls up all the items that are not parents as well as multiply the quantities in with the following query:

    SELECT itemNumber, quantity, cost, sum(item.cost * item.quantity) AS final_rollup
    FROM item
    WHERE itemNumber NOT IN
    (SELECT DISTINCT parentItem
    FROM item
    WHERE parentItem IS NOT NULL)
    group by itemnumber with rollup;


    What I just realized what that was not going to be enough because I also need to account for some parent items having quantities that are more than 1. The final roll up amount with my current query is 22, while the correct amount should be 33. I'm not even sure if what I am trying to do is possible with my table structure.
    So basically if the quantity of a parent item is > 1 than the (cost * quantity) of the non parent items needs to be multiplied again.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on your test data, 22 appears to be the correct answer to me.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •