Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Products, Sections and Sub-Sections

    I have two tables: products and sections.

    A product has make, model and year. These are car/van parts

    Sections have a parent-child relationship. The idea is that these will mimic all the makes, models and years for all products.

    Given a list of new products I have to create all the missing sections. The biggest headache is that the "year" subsection can be repeated.
    For example I could have an AUDI A2 door that is for 2001 models only and a Volkswagen Beetle bonnet that is also for 2001 models only, but these need separate sections creating due to the parent child relationships.


    Set up:
    Code:
    CREATE TABLE products (
       make        varchar(50)   default NULL
     , model       varchar(50)   default NULL
     , years       varchar(50)   default NULL
     , code        varchar(50)   default NULL
     , description varchar(250)  default NULL
     , ex_vat      decimal(10,2) default NULL
     , inc_vat     decimal(10,2) default NULL
     , supplier    varchar(40)   default NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    
    INSERT INTO products (make, model, years, code, description) VALUES ('AUDI', 'ALFASUD', '83-94', 'ABCDEFG', 'TEST')
    
    CREATE TABLE jss_sections (
       sectionid mediumint(9) NOT NULL
     , title     varchar(250) default NULL
     , parent    mediumint(9) default NULL
    ) ENGINE=MyISAM AUTO_INCREMENT=388 DEFAULT CHARSET=latin1
    
    
    INSERT INTO jss_sections (sectionid, title, parent) VALUES (387, '83-94', 386)
    INSERT INTO jss_sections (sectionid, title, parent) VALUES (386, 'ALFASUD', 385)
    INSERT INTO jss_sections (sectionid, title, parent) VALUES (385, 'AUDI', NULL)
    What I have come up with so far:
    Code:
    SELECT *
    FROM   products
     INNER
      JOIN (
            SELECT years.title As years
                 , years.sectionid As years_id
                 , model.title As model
                 , model.sectionid As model_id
                 , make.title As make
                 , make.sectionid As make_id
            FROM   jss_sections As years
             LEFT
              JOIN jss_sections As model
                ON model.sectionid = years.parent
             LEFT
              JOIN jss_sections As make
                ON make.sectionid = model.parent
           ) As sections
        ON sections.make  = products.make
       AND sections.model = products.model
       AND sections.years = products.years
    If I remove the last two lines, I get a result (i.e. only matching on make).
    I cannot work out why this is!

    Cheers,
    George
    Last edited by gvee; 07-24-11 at 11:07.
    George
    Home | Blog

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    based on the sample data you provided, the jss_sections rows don't have a valid parent

    so your LEFT OUTER JOINs are okay, but will return only years, no models, no makes

    then of course the INNER JOIN to products will fail where you are trying to match those NULL columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sorry, the sample data is incorrect. It should be
    Code:
    INSERT INTO jss_sections (sectionid, title, parent) VALUES (387, '83-94', 386)
    INSERT INTO jss_sections (sectionid, title, parent) VALUES (386, 'ALFASUD', 385)
    INSERT INTO jss_sections (sectionid, title, parent) VALUES (385, 'AUDI', NULL)
    I have updated the inital post to reflect this also.

    Meaning that the subquery returns the following dataset
    Code:
    years   years_id   model     model_id   make
    83-94   387        ALFASUD   386        AUDI
    Last edited by gvee; 07-24-11 at 11:12.
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, based on tha revision, your subquery with the LEFT OUTER JOINs returns this --
    Code:
    years     years_id    model     model_id    make     make_id
    83-94     387         ALFASUD   386         AUDI     385
    ALFASUD   386         AUDI      385         NULL     NULL
    AUDI      385         NULL      NULL        NULL     NULL
    when i now run your original query, i get
    Code:
    make   model     years   code      description   ex_vat   inc_vat   supplier   years   years_id   model     model_id   make   make_id
    AUDI   ALFASUD   83-94   ABCDEFG   TEST          NULL     NULL      NULL       83-94   387        ALFASUD   386        AUDI   385
    what was your question again?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Found the mistake. My query was fine, it was the data that had been manually entered in to the jss_sections table that was incorrect which meant I too copied the wrong section names across!

    Now that my query is returning what I was wanting I have to write something to find what extra sections need creating. Any ideas?
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT DISTINCT make, model, years FROM products

    then do a left join to your "sections" subquery (which has the 3-level hierarchy of joins)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yeah, that seems obvious (clearly stared at this too long now!).

    However I realise a new trouble... consider this

    I have the following sections
    BMW > 3 SERIES > 91-98
    BMW > 3 SERIES > 98-05
    CITROEN > C1 > 05 on

    A new product is added BMW > 3 SERIES > 05 on
    I therefore only need to add the "05 on" section, under the 3 SERIES header only.

    Hmm...
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    For simplicities sake, let's only use 2 levels of heirarchy; make and model.

    The makes are actually really simple:
    Code:
    SELECT DISTINCT
           products.make
    FROM   products
     LEFT
      JOIN jss_sections
        ON jss_sections.title = products.make
       AND jss_sections.parent IS NULL
    WHERE  jss_sections.title IS NULL
    Ignore the years for now - I figure they will follow the same process as models, once those have been solved.
    George
    Home | Blog

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Still not cracked this. I can't work out how to create the missing subsections (model)

    *bump*
    George
    Home | Blog

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think I may have got the models done... Extending this for the years is going to be a paaaaaaaaain...

    Please, if anyone has better suggestions on how to approach and solve this then let me know!
    Code:
    SELECT new_models.model
         , makes.sectionid As parent
         , makes.title As make
    FROM   (
            SELECT *
            FROM   products
            WHERE  parent = 1
           ) As makes
     INNER
      JOIN (
            SELECT DISTINCT
                   products.make
                 , products.model
            FROM   products
             LEFT
              JOIN (
                    SELECT makes.title As make
                         , models.parent As make_id
                         , models.title As model
                         , models.sectionid As model_id
                    FROM   jss_sections As makes
                     LEFT
                      JOIN jss_sections As models
                        ON models.parent = makes.sectionid
                   ) As sections
                ON sections.make = products.make
               AND sections.model = products.model
            WHERE  sections.model IS NULL
           ) As new_models
        ON new_models.make = makes.title
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    BOOM
    Code:
    INSERT INTO gv_sections (title, parent)
    SELECT new_years.years
         , makes_and_models.model_id As parent
    FROM   (
            SELECT make.title As make
                 , make.sectionid As make_id
                 , models.title As model
                 , models.sectionid As model_id
            FROM   jss_sections As make
             INNER
              JOIN jss_sections As models
                ON models.parent = make.sectionid
           ) As makes_and_models
     INNER
      JOIN (
            SELECT DISTINCT
                   products.make
                 , products.model
                 , products.years
            FROM   products
             LEFT
              JOIN (
                    SELECT makes.title As make
                         , models.parent As make_id
                         , models.title As model
                         , models.sectionid As model_id
                         , years.title As years
                         , years.sectionid As years_id
                    FROM   jss_sections As makes
                     LEFT
                      JOIN jss_sections As models
                        ON models.parent = makes.sectionid
                     LEFT
                      JOIN jss_sections As years
                        ON years.parent = models.sectionid
                   ) As sections
                ON sections.make = gv_import.make
               AND sections.model = gv_import.model
               AND sections.years = gv_import.years
            WHERE  sections.years IS NULL
           ) As new_years
        ON new_years.model = makes_and_models.model
       AND new_years.make  = makes_and_models.make






    Still have to test these and tiy up the superflous columns and stuff but this is looking like a goer!

    I just have to run them in order and I should get everything I need for now.

    This day has been too long.
    Last edited by gvee; 07-24-11 at 18:24.
    George
    Home | Blog

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    whoa, george, sorry i wasn't able to help you through this, i was out all day (nasty sunburn, too)

    glad you got it sorted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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