SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Apr 2008
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Okay, if I understand you correctly, you have main categories, and those main categories have sub-categories. Then, you have companies which can pick any of those, either from the main or sub-categories.

    When you go to count them up, you want to have it counted just once each time, for the main categories. However, having a company show up in two different main categories is fine, just as long as they don't show up twice for the same main category.

    If that is the case, then there are a few changes to your database structure that would be helpful.

    First, you should split main categories and sub categories into different tables, like this:
    Code:
    CREATE TABLE `categories` (
        `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        `name` VARCHAR(40) NOT NULL
    );
    
    CREATE TABLE `sub_categories` (
        `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        `parent_category` INT NOT NULL,
        `name` VARCHAR(40) NOT NULL
    );
    This will not only save you space, it also looks cleaner and is much more flexible (it allows more than, and fewer than 3 sub_categories).

    Also, for your companies, you can improve it's cleanness and flexibility by doing splitting it into two tables, for similar reasons:
    Code:
    CREATE TABLE `company_details` (
        `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        `name` VARCHAR(40) NOT NULL,
        `fax_no` VARCHAR(20),
        `email` VARCHAR(40) NOT NULL
    );
    
    CREATE TABLE `company_categories` (
        `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        `company_id` INT NOT NULL,
        `category_id` INT NOT NULL,
        `is_sub_category` BOOLEAN DEFAULT false
    );
    (If you note, I made email 40 instead of 20, because 20 could be a bit too small for some).

    The is_sub_category tells us whether the id in category_id refers to categories or sub_categories.

    Splitting them up in this way allows them to have less than, or more than 6 entries. If you want them to only have a maximum of 6 entries, you would just check it in your code.

    Also, if you notice, no where other than the categories and sub_categories table do I refer to the categories' names. That way, if you change a name in one place, you don't have to change it in all of the places.

    Now, your query becomes a lot simpler to accomplish:
    Code:
    SELECT c.`id`, c.`name`, COUNT(company_categories.id) as record_id
    FROM `categories` c
    LEFT JOIN `sub_categories` s
    ON c.`id` = s.`parent_id`
    GROUP BY c.id, s.parent_id
    I'm not totally sure on that query, but that should be pretty close.

    Hope that helps.

  3. #3
    SitePoint Member
    Join Date
    Apr 2008
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I Hear exactally what you are saying re category table. When I initially built the table I gave no thought to auctually removing the info later.

    (the tables are just temp tables for this example, eg email short etc all swt in original tables.)

    If I split the category tables into two, main and sub I would have a problem with the ID numbers, eg would be like this

    Main Cat
    1 Accommodation
    2 Automotive

    Sub Cats
    1 Booking

    but there is prob a way around it. i will create those tables and try that query you gave me, if it works then ill change my database, thanks for getting back to me, ill let you know how it goes.

  4. #4
    SitePoint Member
    Join Date
    Apr 2008
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    have gone over your idea, still leaves me with the original problem that im trying to solve, which is

    I need it to
    Select all sub categories of accommadation

    then
    count record_id where cat1ID-Cat6ID = any ID numbers of accommodation or its sub cats, but only select each company record once per main category

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    hamish, i suggest you abandon that design and start over

    see Categories and Subcategories
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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