Table with catogories and multiple sub catogories for each catogory

Question

I am trying to write a new application but I'm stuck. I want to create a database with a table for storing catogories and another one for storing sub categories. I have some ideas about how to create the database/tables but no idea about how I can select categories and all sub categories belonging to that category at once. Can someone help me?

Scenario:

• Table with Users identified by ID's.

• Table with Categories identified by ID and with a foreign key to the User.ID (one category can belong to only one user)

• Table with SubCategories with foreign keys to the Category table it belongs to and the User.ID that the Category belongs to.

How could I select and display all usernames with belonging categories and sub categories?

Answer 1

If I understand your question correctly, you want a query that will give you results like the following:

User Category SubCategories
Bob | Builder | BuilderSub1
Bob | Builder | BuilderSub2
Jerry | Supervisor | SupervisorSub1
Tim | Builder | BuilderSub3

SELECT u.UserName as 'User', c.CategoryName as 'Category', sc.SubcategoryName as 'SubCategories'     
FROM Users u 
INNER JOIN Category c ON c.UserId = u.Id 
INNER JOIN Subcategory sc ON
sc.CategoryId = c.Id  
GROUP BY sc.SubcategoryName, c.CategoryName, u.UserName

The GROUP BY is what you need.

Answer 2

You'll need a foreign key relationship and a JOIN:

create table category (
    id int not null auto_increment,
    primary key(id)
);

create table subcategory (
    id int not null auto_increment,
    category_id int,
    primary key(id),
    foreign key(category_id) references category(id)
);

Here's a sample JOIN:

select *
from user
join category
on user.category_id = category.id
join subcategory
on category.id = subcategory.category_id
where user.id = ?