Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2008
    Posts
    8

    Unanswered: difficult mysql compare / select statement

    I am using MySql 5.0.51a and am trying to acheive the following

    I have 2 tables, one of categories and one of company details, each company can select up to 6 different categories for their business.

    In my category table, I have sub categories, and sub sub categories, eg,
    Main Category - Accommodation
    Sub Category - Hotels
    Sub Sub - 4 Star Hotels

    What I want to do is run through my company details table and count the number of people who have listed themselves in accommodation, hotels or 4 star hotels. I have this all working (yay) but the problem I am encountering is that if a company selected hotels as their first category and 4 star hotels as their second cateogory, my query says I have 1 in hotels and 1 in 4 star hotels, eg, 2 listings, but in reality I only have 1. I have drawn up the following table creation codes so you can have a fiddle.

    Categories Table
    Code:
    CREATE TABLE `categories` (
      `ID` int(20) NOT NULL,
      `Cat_Name` varchar(200) NOT NULL,
      `2nd_ID` varchar(20) NOT NULL,
      `2nd_Cat_Name` varchar(200) NOT NULL,
      `3rd_ID` varchar(20) NOT NULL,
      `3rd_name` varchar(200) NOT NULL,
      `Name` varchar(200) NOT NULL,
      KEY `ID` (`ID`),
      KEY `Name` (`Name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    -- 
    -- Dumping data for table `categories`
    -- 
    
    INSERT INTO `categories` (`ID`, `Cat_Name`, `2nd_ID`, `2nd_Cat_Name`, `3rd_ID`, `3rd_name`, `Name`) VALUES 
    (1, 'Accommodation', '', '', '', '', 'Accommodation'),
    (2, '', '1', 'Booking', '', '', 'Booking'),
    (3, '', '1', '', '2', 'Online Booking', 'Online Booking'),
    (4, '', '1', '', '2', 'Offline Booking', 'Offline Booking'),
    (5, '', '1', 'Hotels', '', '', 'Hotels'),
    (6, '', '1', '', '5', '5 Star', '5 star'),
    (7, '', '1', '', '5', '4 Star', '4 Star'),
    (8, 'Automotive', '', '', '', '', 'Automotive'),
    (9, '', '8', 'Auto Accessories', '', '', 'Auto Accessories'),
    (10, '', '8', '', '9', 'Car Audio Systems', 'Car Audio Systems'),
    (11, '', '8', '', '9', 'Car Care Products', 'Car Care Products'),
    (12, '', '8', '', '9', 'Sunroofs', 'Sunroofs'),
    (13, '', '8', 'Driver Education', '', '', 'Driver Education'),
    (14, '', '8', '', '13', 'Defensive Driving', 'Defensive Driving'),
    (15, '', '8', '', '13', 'Driver Training', 'Driver Training'),
    (16, '', '8', 'Insurance', '', '', 'Insurance'),
    (17, 'Home And Garden', '', '', '', '', 'Home And Garden'),
    (18, '', '17', 'Garden', '', '', 'Garden'),
    (19, '', '17', '', '18', 'Ready Grass', 'Ready Grass'),
    (20, '', '17', '', '18', 'Fish Ponds', 'Fish Ponds'),
    (21, '', '17', 'Home', '', '', 'Home'),
    (22, '', '17', '', '21', 'Appliances', 'Appliances'),
    (23, '', '17', '', '21', 'Couches', 'Couches'),
    (24, '', '17', '', '21', 'Kitchen', 'Kitchen'),
    (25, '', '17', '', '21', 'Cleaning', 'Cleaning');
    Company Details Table
    Code:
    CREATE TABLE `company_details` (
      `Record_ID` int(50) NOT NULL,
      `Company_Name` varchar(100) NOT NULL,
      `Category1_ID` varchar(10) NOT NULL,
      `Category2_ID` varchar(10) NOT NULL,
      `Category3_ID` varchar(10) NOT NULL,
      `Category4_ID` varchar(10) NOT NULL,
      `Category5_ID` varchar(10) NOT NULL,
      `Category6_ID` varchar(10) NOT NULL,
      `Category1_Name` varchar(50) NOT NULL,
      `Category2_Name` varchar(50) NOT NULL,
      `Category3_Name` varchar(50) NOT NULL,
      `Category4_Name` varchar(50) NOT NULL,
      `Category5_Name` varchar(50) NOT NULL,
      `Category6_Name` varchar(50) NOT NULL,
      `Fax_No` varchar(20) NOT NULL,
      `Email` varchar(20) NOT NULL,
      KEY `Category1_ID` (`Category1_ID`),
      KEY `Category2_ID` (`Category2_ID`),
      KEY `Category3_ID` (`Category3_ID`),
      KEY `Category4_ID` (`Category4_ID`),
      KEY `Category5_ID` (`Category5_ID`),
      KEY `Category6_ID` (`Category6_ID`),
      KEY `Record_ID` (`Record_ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    -- 
    -- Dumping data for table `company_details`
    -- 
    
    INSERT INTO `company_details` (`Record_ID`, `Company_Name`, `Category1_ID`, `Category2_ID`, `Category3_ID`, `Category4_ID`, `Category5_ID`, `Category6_ID`, `Category1_Name`, `Category2_Name`, `Category3_Name`, `Category4_Name`, `Category5_Name`, `Category6_Name`, `Fax_No`, `Email`) VALUES 
    (1, 'ABC Company', '4', '6', '9', '', '', '', 'Offline Booking', '5 Star', 'Auto Accessories', '', '', '', '09 555 5555', 'test@test.com'),
    (2, 'XYZ Company', '1', '8', '9', '12', '', '', 'Accommodation', 'Automotive', 'Auto Accessories', 'Sunroofs', '', '', '09 555 5555', 'test@test.com'),
    (3, '123 Company', '11', '', '', '', '', '', 'Car Care Products', '', '', '', '', '', '09 555 5555', 'test@test.com'),
    (4, '456 Company', '2', '9', '17', '22', '', '', 'Booking', 'Auto Accessories', 'Home And Garden', 'Appliances', '', '', '09 555 5555', 'test@test.com'),
    (5, 'Joes Company', '12', '22', '5', '6', '', '', 'Sunroofs', 'Appliances', 'Hotels', '5 Star', '', '', '09 555 5555', 'test@test.com'),
    (6, 'Some Place', '20', '', '', '', '', '', 'Fish Ponds', '', '', '', '', '', '09 555 5555', 'test@test.com'),
    (7, 'Some Company', '7', '', '', '', '', '', '4 Star', '', '', '', '', '', '09 555 5555', 'test@test.com'),
    (8, 'Another Company', '2', '3', '4', '7', '10', '19', 'Booking', 'Online Booking', 'Offline Booking', '4 Star', 'Car Audio Systems', 'Ready Grass', '09 555 5555', 'test@test.com'),
    (9, 'This Company', '24', '', '', '', '', '', 'Kitchen', '', '', '', '', '', '09 555 5555', 'test@test.com'),
    (10, 'My Company', '23', '', '', '', '', '', 'Couches', '', '', '', '', '', '09 555 5555', 'test@test.com'),
    (11, 'Ooga Booga', '13', '', '', '', '', '', 'Driver Education', '', '', '', '', '', '09 555 5555', 'test@test.com'),
    (12, 'Pew Pew', '4', '', '', '', '', '', 'Offline Booking', '', '', '', '', '', '09 555 5555', 'test@test.com'),
    (13, 'Key Positions', '16', '11', '', '', '', '', 'Insurance', 'Car Care Products', '', '', '', '', '09 555 5555', 'test@test.com'),
    (14, 'Ze Booking Co', '1', '2', '', '', '', '', 'Accommodation', 'Booking', '', '', '', '', '09 555 5555', ''),
    (15, '5 Star Hotel', '6', '', '', '', '', '', '5 Star', '', '', '', '', '', '', ''),
    (16, 'Repco', '11', '10', '', '', '', '', 'Car Care Products', 'Car Audio Systems', '', '', '', '', '09 555 5555', 'test@test.com');
    MySQL Command is this
    Code:
    Select c.Id, c.Name, @RecordCount:=Count(d.Record_Id)
    From Categories c, company_Details d
    Where (c.Id = Category1_Id
    or c.Id = Category2_Id
    or c.Id = Category3_Id
    or c.Id = Category4_Id
    or c.Id = Category5_Id
    or c.Id = Category6_Id)
    Group By c.Id, c.Name
    Order by Count(d.Record_Id) DESC, c.Name
    and the result is
    ID, Category Name, No Matches
    6, '5 star', 3
    9, 'Auto Accessories', 3
    2, 'Booking', 3
    11, 'Car Care Products', 3
    4, 'Offline Booking', 3
    7, '4 Star', 2
    1, 'Accommodation', 2
    22, 'Appliances', 2
    10, 'Car Audio Systems', 2
    12, 'Sunroofs', 2
    8, 'Automotive', 1
    23, 'Couches', 1
    13, 'Driver Education', 1
    20, 'Fish Ponds', 1
    17, 'Home And Garden', 1
    5, 'Hotels', 1
    16, 'Insurance', 1
    24, 'Kitchen', 1
    3, 'Online Booking', 1
    19, 'Ready Grass', 1

    as an example

    Joes Company (Record ID 5) is listed in 'hotels' and '5 star hotels' so it is counted twice.

    My ideal result will look like this
    Category ID, Category Name, No Matches
    1, Accommodation, 7
    8, Automotive, 9
    17, Home And Garden, 6

    But if someone can just point out how I make the following happen,
    Check the company category IDs against all the ID numbers belonging to Accommodation or any of the sub categories of accommodation, and if there is a match +1 to the total for that main cateogry, then move onto the next Main Category which is Automotive and discard any other matches for Accommodation or its sub categories until it moves onto the next company record.

    I have been banging my head against this problem for almost 24 hours, I would really appreciate some help, I know its not a simple command (Im hoping it is possible)

    Thanks in advance

    a very humble hamish

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hamish, i suggest you abandon that design and start over

    see Categories and Subcategories
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2008
    Posts
    8
    yeah I have no hesitations starting from scratch, thanks for the link seems to be what im looking for.


  4. #4
    Join Date
    Apr 2008
    Posts
    8
    just reading the post the part that i need is

    "Displaying all categories and subcategories: site maps and navigation bars"

    but what I dont get is why I have to have 4 diff tables
    root
    down1
    down2
    down3
    down4

    I have made the same thing into one table because I want the categories to have distinct record_ids if I split it up into 4 tables then each table will begin at record 1 which messes up all the other parts of my system.

    And then im still going to end up with the exact same problem that i have now, which is if some bastard client decides that he wants to be listed in (using the example on the link you gave me)
    animal
    and doggie
    and herding
    and collie

    then when I select all companies and count up how many in each category, the same company will be listed 4 times

  5. #5
    Join Date
    Apr 2008
    Posts
    8
    all my table has done is record the data in the exact way that the select statement (as per your link) out puts it.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by nevets_steven
    but what I dont get is why I have to have 4 diff tables
    root
    down1
    down2
    down3
    down4(
    those are not 4 different tables (or even 5)

    there is only one table

    categories and subcategories share the same table

    subcategories merely point to their parent category
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2008
    Posts
    8
    im not going to pretend that I understand because I clearly dont

    so to make it easier, I have used the table design as per the link you gave me,
    Code:
    CREATE TABLE `categories` (
      `id` int(11) NOT NULL,
      `name` varchar(37) NOT NULL,
      `parentid` int(11) default NULL,
      PRIMARY KEY  (`id`),
      KEY `parentid_fk` (`parentid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    
    
    INSERT INTO `categories` (`id`, `name`, `parentid`) VALUES 
    (1, 'animal', NULL),
    (2, 'Vegetable', NULL),
    (3, 'mineral', NULL),
    (4, 'doggie', 1),
    (5, 'kittie', 1),
    (6, 'horsie', 1),
    (7, 'gerbil', 1),
    (8, 'birdie', 1),
    (9, 'hunting', 4),
    (10, 'companion', 4),
    (11, 'herding', 4),
    (12, 'setter', 9),
    (13, 'pointer', 9),
    (14, 'terrier', 9),
    (15, 'poodle', 10),
    (16, 'chihuahua', 10),
    (17, 'shepperd', 11),
    (18, 'collie', 11),
    (19, 'tomato', 2),
    (20, 'potato', 2),
    (21, 'quartz', 3),
    (22, 'feldspar', 3);
    
    
    
    CREATE TABLE `petstore` (
      `ID` int(10) NOT NULL,
      `Store_Name` varchar(50) NOT NULL,
      `Pets_available1` varchar(10) NOT NULL,
      `Pets_available2` varchar(10) NOT NULL,
      `Pets_available3` varchar(10) NOT NULL,
      `Pets_available4` varchar(10) NOT NULL,
      `Pets_available5` varchar(10) NOT NULL,
      `Pets_available6` varchar(10) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    
    
    INSERT INTO `petstore` (`ID`, `Store_Name`, `Pets_available1`, `Pets_available2`, `Pets_available3`, `Pets_available4`, `Pets_available5`, `Pets_available6`) VALUES 
    (1, 'Joes Pets', '1', '4', '10', '15', '5', '7'),
    (2, 'Mikes Pets', '10', '11', '12', '13', '14', '9'),
    (3, 'Joesphs Pets', '1', '2', '3', '19', '20', '21'),
    (4, 'Hamishs Pet Store', '2', '3', '19', '20', '21', '4'),
    (5, 'Your Pets', '1', '3', '', '', '', '');
    So now you can grab either of the select statements from the link and run them against this DB and the results are the same as on their page.

    And what I need to do is to compare that category list to the list of pet stores, to see count the number of pet stores that sell Animals, Minerals and Vegetables.

    The result should look like this

    ID, Category Name, No Places That Sell That
    1, Animal, 5
    2, Vegetable, 2
    3, Mineral, 3

    5 Places sell animals, 3 sell minerals, 2 sell vegetabls
    Joes (6 different kinds of animals) A
    Mikes (6 different kinds of animals) A
    Joesphs (1 pet, 3 veges, 2 minerals) A,V,M
    Hamishs (1 pet, 3 veges, 2 minerals) A,V,M
    Your Pet (1 pet, 1 mineral) A,M

  8. #8
    Join Date
    Apr 2008
    Posts
    8
    If I run my command against the database I get back

    1, 'animal', 3
    3, 'mineral', 3
    10, 'companion', 2
    4, 'doggie', 2
    20, 'potato', 2
    21, 'quartz', 2
    19, 'tomato', 2
    2, 'Vegetable', 2
    7, 'gerbil', 1
    11, 'herding', 1
    9, 'hunting', 1
    5, 'kittie', 1
    13, 'pointer', 1
    15, 'poodle', 1
    12, 'setter', 1
    14, 'terrier', 1

    and If I add these up to only show the parent category I get
    1, Animal, 15
    2 Vegetable, 6
    3, Mineral, 5

    -- my command
    Select c.Id, c.Name, @RecordCount:=Count(p.Id)
    From Categories c, petstore p
    Where (c.Id = Pets_available1
    or c.Id = Pets_available2
    or c.Id = Pets_available3
    or c.Id = Pets_available4
    or c.Id = Pets_available5
    or c.Id = Pets_available6)
    Group By c.Id, c.Name
    Order by Count(p.Id) DESC, c.Name

  9. #9
    Join Date
    Apr 2008
    Posts
    8
    What im trying to do is make a site like

    www.ubd.co.nz

    thats the result im looking for

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by nevets_steven
    What im trying to do is make a site like

    www.ubd.co.nz

    thats the result im looking for
    well, i should sincerely hope that you do a better job of it than they have

    on their home page, they show...
    Computers & Electronics (5588)
    but if you click on it, it shows...
    Results for “Computers & Electronics” located in “New Zealand”

    Showing 1–25 of 5596 results
    when i see crap like that, i typically leave the site
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2008
    Posts
    8
    I dont often blow my own trumpet, but im pretty impresed with what I have put together. Its all in flash (AS2) and exports csv or tab files once payment received.

    At the moment I have manually written the categories in the .fla file and just run a whole pile of counts, just want to replace it with a dynamicly created array


    also, their site is exceptionally slow, it can often timeout when moving into sub categories.

Posting Permissions

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