Category, sub, link

[haarvik]

Hope I am in the right area.  I am working on a project which is sort of a business directory.  I have a category table, a sub-category table then the actual account table.  So a user clicks on the category, then selects the sub-category to view information of accounts in that category, and then be able to click to see the full profile.  Here's where I am getting stuck.  A user may be listed in more than one sub.  As an example, let's say a parent is Cars, then subs might be New, Used, Parts, etc.  Now a particular user might be listed under all three subs.  How can I link them?  I have my cat/sub linked via parent/child id's.  However, how can I get the user to be linked to multiple subs?  Would I need to create a separate table to do similar to parent/child of the categories?  If so, then how would I insert the multiple id's into the table?  I assume at this point that I would use drop downs for the subs.  I am having a real hard time wrapping my head around this so any help is greatly appreciated.

[MadTechie]

If i have read this correctly then you have some category and they in turn have sub-category,

for example

CatA

--SubCat1

--SubCat2

--SubCat3

CatB

--SubCat4

--SubCat5

--SubCat6

 

Now for example you want UserA that belongs to CatA

and UserB who belong to SubCat2 & SubCat5 & SubCat6

 

Now if you have a table like this

 

categories

ID Name ParentID

1 CatA 0

2 CatB 0

3 SubCat1 1

4 SubCat2 1

5 SubCat3 1

6 SubCat4 2

7 SubCat5 2

8 SubCat6 2

 

Users

ID Name CatID

1 UserA 1

1 UserB ???

 

Can you can do is create another table called UserCats

 

UserCats

ID UserID CatID

1 1 1

2 2 4

3 2 7

4 2 8

 

Now you can get all the Cats like this

SELECT categories.Name

FROM UserCats

LEFT JOIN categories ON categories.ID = UserCats.CatID

WHERE UserCats.UserID = 2

 

 

Hope that helps.. or did i miss the whole point ?