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