difficult mysql compare / select statement

Question

nevets_steven 0 Newbie Poster

15 Years Ago

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

mysql

2 Contributors
1 Reply
103 Views
20 Hours Discussion Span
Latest Post 15 Years Ago Latest Post by tesuji

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

tesuji 135 Master Poster · Answer 1 · 2008-04-30T09:32:30+00:00

Hi nevets_steven,

your both tables do not satisfy the requirements of the First Normal Form (1NF), which is the minimum requirement, because they have countless repeating groups. Therefore, it's really hard to construct appropriate Queries, to prevent inconsistent data or to win upper hand against hidden anomalies.

To get rid of this dilemma, doing serious normalization should be your prime step before trying to create sql selects for such poor tables. You may google for normalization, repeating groups, insert anomalies ... to get a true impression.

brs,
cliff