Products, Sections and Sub-Sections

gvee · #1 (**permalink**) 07-24-11, 09:52

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

r937 · #2 (**permalink**) 07-24-11, 10:02

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

gvee · #3 (**permalink**) 07-24-11, 10:07

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

r937 · #4 (**permalink**) 07-24-11, 10:15

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?

gvee · #5 (**permalink**) 07-24-11, 10:21

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?

r937 · #6 (**permalink**) 07-24-11, 10:23

SELECT DISTINCT make, model, years FROM products

then do a left join to your "sections" subquery (which has the 3-level hierarchy of joins)

gvee · #7 (**permalink**) 07-24-11, 10:37

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...

gvee · #8 (**permalink**) 07-24-11, 11:00

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.

gvee · #9 (**permalink**) 07-24-11, 14:37

Still not cracked this. I can't work out how to create the missing subsections (model)

*bump*

gvee · #10 (**permalink**) 07-24-11, 16:19

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

gvee · #11 (**permalink**) 07-24-11, 17:20

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.

r937 · #12 (**permalink**) 07-24-11, 17:36

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

Internet Services

Web Development

Digital Marketing

Consumer Tech