Combining N:N with 1:N relationships

[Autoclave]

Hello dear friends!
I have the following issue designing the relationships in my db.

N:N relationship
I have a list of companies and a list of tags.
Each COMPANY can have multiple TAGS, the same TAG can be assigned to multiple COMPANIES.

1:N relationship
One TAG can have multiple SUBTAGS.

Now, COMPANIES and SUBTAGS relationships:


One COMPANY can have 0-to-many SUBTAGS within one TAG.

How do I design this relationship? Companies, Tags, and Subtags are one table each.

Thank you very much in advance.

[pbaldy]

What you describe (the first one) is a many-to-many relationship. That is typically handled with a junction table (you'd still have the other 2). Let's call it "CompanyTags". It would have fields for the company ID field and the Tags ID field, and typically it's own ID field.

[Autoclave]

I have some experience with Many-to-many relantionships and junction tables. So this is not really the problem for me. The problem is how to connect the SUBTAGS to all this, which are N:1 with Tags.

[pbaldy]

Unless I'm missing something, that would simply be a one-to-many relationship with Tags. No direct relationship with the other tables.

[goodguy]

To view all the subtags for one company's tags, write a query like this:
SELECT C.COMPANYNAME, T.TAGNAME, S.SUBTAGNAME FROM C AS COMPANIES INNER JOIN ((COMPANYTAGS AS CT ON C.COMPANYID = CT.COMPANYID) INNER JOIN TAGS AS T ON T.TAGID = CT.TAGID) INNER JOIN SUBTAGS AS S ON S.SUBTAGID = S.TAGID.

[Autoclave]

Thank you, goodguy, I will try it ASAP, and report if it works exactly as I want.