Thread: Recursive query

    #1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,118
    Rep Power
    537

    Recursive query


    I have the following tables. The application will enforce that one and only one of tables pointsReal, pointsVirtual, or pointsNotApplicable will be joined to points. pointsNotApplicable is well, not applicable, and the application will prevent it from ever being joined to pointsVirtual_has_points, and only pointsReal and pointsVirtual will be joined to pointsVirtual_has_points.

    How can I return all records in the points table which meet the following criteria:

    1. Limited to a LIKE clause on points.name (don't need any help on this one)

    AND (

    2. Limit to where pointsVirtual_has_points JOINs points to pointsReal and pointsReal.trend is not zero (this one is also okay)

    OR

    3. Limit to where pointsVirtual_has_points JOINs points to pointsVirtual, and pointsVirtual_has_points isn't JOINing the resultant record to pointsReal where pointsReal.trend is zero. Note that pointsVirtual_has_points can be recursively JOINed to another pointsVirtual, however, it is expected that it will never exceed four or so levels.
    )

    Thank you!

    Capture.PNG

    Code:
    CREATE TABLE IF NOT EXISTS points (
      id INT NOT NULL,
      name VARCHAR(45) NOT NULL,
      type ENUM('real', 'virtual', 'NotApplicable') NOT NULL,
      PRIMARY KEY (id))
    ENGINE = InnoDB;
    
    CREATE TABLE IF NOT EXISTS pointsReal (
      id INT NOT NULL,
      trend TINYINT NOT NULL,
      PRIMARY KEY (id),
      CONSTRAINT fk_pointsReal_points
        FOREIGN KEY (id)
        REFERENCES points (id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    CREATE TABLE IF NOT EXISTS pointsVirtual (
      id INT NOT NULL,
      PRIMARY KEY (id),
      CONSTRAINT fk_pointsVirtual_points1
        FOREIGN KEY (id)
        REFERENCES points (id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    
    CREATE TABLE IF NOT EXISTS pointsVirtual_has_points (
      pointsVirtual_id INT NOT NULL,
      points_id INT NOT NULL,
      PRIMARY KEY (pointsVirtual_id, points_id),
      INDEX fk_pointsVirtual_has_points_points1_idx (points_id ASC),
      INDEX fk_pointsVirtual_has_points_pointsVirtual1_idx (pointsVirtual_id ASC),
      CONSTRAINT fk_pointsVirtual_has_points_pointsVirtual1
        FOREIGN KEY (pointsVirtual_id)
        REFERENCES pointsVirtual (id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT fk_pointsVirtual_has_points_points1
        FOREIGN KEY (points_id)
        REFERENCES points (id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    CREATE TABLE IF NOT EXISTS pointsNotApplicable (
      id INT NOT NULL,
      bla VARCHAR(45) NULL,
      PRIMARY KEY (id),
      CONSTRAINT fk_pointsNotApplicable_points1
        FOREIGN KEY (id)
        REFERENCES points (id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,118
    Rep Power
    537
    Maybe an analogy will help.

    Both canisters (pointsVirtual) and marbles (pointsReal) are things (points). Canisters can contain both other canisters as well as marbles. I wish to return all non-blue marbles as well as all canisters which do not contain blue marbles or contain containers which contain blue marbles.

IMN logo majestic logo threadwatch logo seochat tools logo