difficult mysql compare / select statement

nevets_steven · #1 (**permalink**) 04-29-08, 08:59

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

r937 · #2 (**permalink**) 04-29-08, 16:18

hamish, i suggest you abandon that design and start over

see Categories and Subcategories

nevets_steven · #3 (**permalink**) 04-29-08, 16:21

yeah I have no hesitations starting from scratch, thanks for the link seems to be what im looking for.

nevets_steven · #4 (**permalink**) 04-29-08, 16:27

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

nevets_steven · #5 (**permalink**) 04-29-08, 16:31

all my table has done is record the data in the exact way that the select statement (as per your link) out puts it.

r937 · #6 (**permalink**) 04-29-08, 16:36

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

nevets_steven · #7 (**permalink**) 04-29-08, 17:03

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

nevets_steven · #8 (**permalink**) 04-29-08, 17:11

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

nevets_steven · #9 (**permalink**) 04-29-08, 17:13

What im trying to do is make a site like

www.ubd.co.nz

thats the result im looking for

r937 · #10 (**permalink**) 04-29-08, 17:24

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

Quote:

Computers & Electronics (5588)

but if you click on it, it shows...

Quote:

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

nevets_steven · #11 (**permalink**) 04-29-08, 21:48

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech