•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 397,838 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,583 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MySQL advertiser:
Views: 524 | Replies: 1
 |
•
•
Join Date: Apr 2008
Posts: 1
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
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
Company Details Table
MySQL Command is this
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
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
•
•
Join Date: Apr 2008
Posts: 295
Reputation:
Rep Power: 1
Solved Threads: 41
Posting Whiz in Training
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
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
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MySQL Marketplace
Linear Mode
Other Threads in the MySQL Forum
- Previous Thread: Question about Update function
- Next Thread: Could not load module mysql (Windows Xp Sp2)
