Tricky MySQL query question

LotsOfZeros

LotsOfZeros

^^^ Bi-Winning ^^^
Feb 9, 2008
4,649
118
0
www.makemoniesonline.com
I am having a problem trying to write a query to pull what would normally be a JOIN into the result set. It's actually a column reference to another record within the same table.

My tables with their columns:
Code: 
business_table
 business_id
 business_name
 category_id

category_table
 category_id
 category_name
 parent_category
The parent_category column refers back to category_id of a different record within the same table

My query:
Code: 
SELECT * FROM business_table 
 JOIN category_table 
 ON business_table.category_id = category_table.category_id 
 ORDER BY business_id DESC LIMIT 3
My hypothetical result:
Code: 
-----------------------------------------------------------------------------------------------
| business_id | business_name | category_id | category_id_1 | category_name | parent_category |
-----------------------------------------------------------------------------------------------
| 011         | Smith Plumbing|     13      |      13       |  Plumbers     |       10        |
| 010         | Hair Care Zone|     22      |      22       |  Salons       |       03        |
| 008         | Taco Bell     |     45      |      45       |  Mexican Food |       06        |
-----------------------------------------------------------------------------------------------
I need the get the category_name of the parent_category to show up as the name (not the category id number)

I am assuming this would have to be represented as a subquery within the main query of some sort but I'm having a difficult time conceptualizing it.

The circular reference of this is making my head explode.
 


LotsOfZeros said:
I am having a problem trying to write a query to pull what would normally be a JOIN into the result set. It's actually a column reference to another record within the same table.

My tables with their columns:
Code: 
business_table
 business_id
 business_name
 category_id

category_table
 category_id
 category_name
 parent_category
The parent_category column refers back to category_id of a different record within the same table

My query:
Code: 
SELECT * FROM business_table 
 JOIN category_table 
 ON business_table.category_id = category_table.category_id 
 ORDER BY business_id DESC LIMIT 3
My hypothetical result:
Code: 
-----------------------------------------------------------------------------------------------
| business_id | business_name | category_id | category_id_1 | category_name | parent_category |
-----------------------------------------------------------------------------------------------
| 011         | Smith Plumbing|     13      |      13       |  Plumbers     |       10        |
| 010         | Hair Care Zone|     22      |      22       |  Salons       |       03        |
| 008         | Taco Bell     |     45      |      45       |  Mexican Food |       06        |
-----------------------------------------------------------------------------------------------
I need the get the category_name of the parent_category to show up as the name (not the category id number)

I am assuming this would have to be represented as a subquery within the main query of some sort but I'm having a difficult time conceptualizing it.

The circular reference of this is making my head explode.
Click to expand...

Try this? idk if it's valid, but i think this is what you're missing

SELECT b.*, c.name as `parent_category` FROM business_table as b JOIN category_table as c ON b.category_id = c.id ORDER BY b.id DESC LIMIT 3
 
uplinked said:
Try this? idk if it's valid, but i think this is what you're missing

SELECT b.*, c.name as `parent_category` FROM business_table as b JOIN category_table as c ON b.category_id = c.id ORDER BY b.id DESC LIMIT 3
Click to expand...

Definitely a step in the right direction but that query just replaced parent_category results with the original results showing up in category_name while removing category_name column altogether.

Code: 
-----------------------------------------------------------------------------
| business_id | business_name | category_id | category_id_1 |parent_category|
-----------------------------------------------------------------------------
| 011         | Smith Plumbing|     13      |      13       |  Plumbers     |
| 010         | Hair Care Zone|     22      |      22       |  Salons       |
| 008         | Taco Bell     |     45      |      45       |  Mexican Food |
-----------------------------------------------------------------------------
 
SELECT business_table.*, cat.category_name as category, pcat.category_name as parent_category FROM business_table JOIN category_table cat USING (category_id) JOIN category_table pcat ON cat.category_id = pcat.category_id ORDER BY business_id DESC LIMIT 3

Little tired right now, so there might be a mistake or two....
 
If I read the requirements correctly, this should do the trick. You don't specify how you want to handle multiple category levels, which would cause the business rows to repeat for each level in the hierarchy.
Code: 
CREATE TABLE `businesses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)
INSERT INTO `businesses` (`id`, `name`, `category_id`) VALUES
(11, 'Smith Plumbing', 13),
(10, 'Hair Care Zone', 22),
(8, 'Taco Bell', 45),
(33, 'Joe Plumbers', 13),
(34, 'Random Business', 100)


CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `parent_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO `categories` (`id`, `name`, `parent_id`) VALUES
(13, 'Plumbers', 10),
(10, 'Plumbers Parent', 0),
(22, 'Salons', 3),
(3, 'Salons Parent', 0),
(45, 'Mexican Food', 6),
(6, 'Mexican Food Parent', 0),
(100, 'Category w/No Parent', 0);



SELECT * 
FROM (
SELECT businesses.id AS business_id, businesses.name AS business_name, businesses.category_id AS business_category_id, categories.id AS category_id, categories.name AS category_name, categories.parent_id AS category_parent_id
FROM businesses, categories
WHERE businesses.category_id = categories.id
) AS business
LEFT JOIN categories ON business.category_parent_id = categories.id

I get the following for a result:

Code: 
business_id	business_name	business_category_id	category_id	category_name		category_parent_id	id		name			parent_id
11		Smith Plumbing	13			13		Plumbers		10			10		Plumbers Parent		0
10		Hair Care Zone	22			22		Salons			3			3		Salons Parent		0
8		Taco Bell	45			45		Mexican Food		6			6		Mexican Food Parent	0
33		Joe Plumbers	13			13		Plumbers		10			10		Plumbers Parent		0
34		Random Business	100			100		Category w/No Parent	0			NULL		NULL			NULL
 
  • Like
Reactions: LotsOfZeros
Insomniac said:
SELECT business_table.*, cat.category_name as category, pcat.category_name as parent_category FROM business_table JOIN category_table cat USING (category_id) JOIN category_table pcat ON cat.category_id = pcat.category_id ORDER BY business_id DESC LIMIT 3

Little tired right now, so there might be a mistake or two....
Click to expand...

Getting much closer. I am no longer seeing the ID number in the parent column but I'm not seeing the parent category, it's just giving me the same information as what's contained in the sub category column:

Code: 
-----------------------------------
| category_name | parent_category |
-----------------------------------
|  Plumbers     |    Plumbers     |
|  Salons       |    Salons       |
|  Mexican Food |    Mexican Food |
-----------------------------------
It needs to give me the parent columns like this:
Code: 
-----------------------------------
| category_name | parent_category |
-----------------------------------
|  Plumbers     |  Construction   |
|  Salons       |    Beauty       |
|  Mexican Food |    Dining       |
-----------------------------------
Somehow it needs to be defined within the self join that I want it to match the id numbers with the parent categories and return those, not to return the same categories I passed as my qualifier (=)
 
Ooops, my mistake, as I said, rather tired...


SELECT business_table.*, cat.category_name as category, pcat.category_name as parent_category FROM business_table JOIN category_table cat USING (category_id) JOIN category_table pcat ON cat.parent_category = pcat.category_id ORDER BY business_id DESC LIMIT 3
 
  • Like
Reactions: LotsOfZeros
cindua said:
If I read the requirements correctly, this should do the trick.
Click to expand...

Insomniac said:
Ooops, my mistake, as I said, rather tired...


SELECT business_table.*, cat.category_name as category, pcat.category_name as parent_category FROM business_table JOIN category_table cat USING (category_id) JOIN category_table pcat ON cat.parent_category = pcat.category_id ORDER BY business_id DESC LIMIT 3
Click to expand...

Whoo Hoo! These both work. Thanks much guys! +REP
 
You must log in or register to reply here.
Top Bottom