CODEWITHSUNDEEP
mysqlsql
praveen
praveen

Reputation: 1

how to get sub categories with category detail?

i have two tables.

tb_category table

CREATE TABLE IF NOT EXISTS `tb_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

tb_subcategory table

CREATE TABLE IF NOT EXISTS `tb_subcategory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

i want to get subcategories for selected category with parent category details.

so i need to get specified category row and it subcategories in single query

thanks in advance

Upvotes: 0

Views: 202

Answers (2)

Devart
Devart

Reputation: 121912

Try to write a query using JOIN clause and bind rows by tb_category.id and tb_subcategory.category_id fields.

Upvotes: 1

David Winant
David Winant

Reputation: 832

SELECT c.name catname,c.id cat, s.id subcat, s.name subname
FROM tb_category c LEFT JOIN tb_subcategory s ON c.id = s.category_id
WHERE c.id = <category>;

This will pull up a single category record, id=, and all the associated subcategory records.

The output will be something like:

catname         cat     subcat     subname
Category-10     10      1          Cat-10 Subcat-1
Category-10     10      2          Cat-10 Subcat-2
Category-10     10      3          Cat-10 Subcat-3

Upvotes: 1

Related Questions