Joins query help

Hi all,

Just after some help with a query (Stored Procedure) I've managed to get wrapped round my head.

The DB is as such:

COMPANY
Company_id
Company_name

COMPANY_GROUP
Group_id
Group_name

USER
User_id
User_name

Bridging tables

COMPANY_GROUP_BRIDGE
company_id
group_id

USER_COMPANY_BRIDGE
user_id
company_id

Basically, the only parameter I have for the query is a User_id.

I need to get the Group linked to the User and return all the companies within that group.

I'vetried reading up on all the join types again but have just got thiscompletely wrapped round my neck. I keep thinking along the lines ofSELECT all the companies linked to all the groups linked to all thecompanies linked to the User_id :s I must be able to dothis without using two Company tables...?

Any help much appreciated,

Pete

I don't think the table structure is good. You only need one bridge table in stead of two. In this one table, just put company_id, group_id, and user_id together. Otherwise, you will join too much.

|||

There seems to be a contradiction here. You wrote

"I need to get the Group linked to the User and return all the companies within that group."

But you don't have any table that stores the users assigned to a company group.

In case a user belongs to a group and not a company, create a table that will store user id and group id.

Then you can write a query to get all the companies linked to the group to which the user belongs

|||

Cheers for the replies guys, you've pretty much asserted what I've been dreading all along.

The DB is a complete messed up and looks like I'm gonna have to completely overhaul it :/

(BTW:It is not currently possible for a user to be linked to a group but nota company. The groups are not in there for this purpose - In fact Imnot sure why there are there!)

|||

Hmm OK,

Any reccomendations on what a DB 'should' look like based on thisveryextremely light spec:

A Distributor can have many Resellers
A Reseller can have many Groups
A Group can have many Companies
A Company can have many Users
A User may belong to many Companies

(Looking at it - it has been designed so that a Company can belong to many Groups but I dont think that would ever happen )

I think the problem originally was that a User may belong to certaincompanies within a group but not neccessarilly all of them - hence theUser is linked to individual companies rather than a Group.

Also, in the admin backend the problem is which companies to show for options such as edit etc.

A Group Administrator may see all the companies within a group but aCompany Administrator may only see the Companies they are associatedwith in the bridge table. Looks like I'll need the User's'access_level' in all the SProcs.

OK, back to basics. My head hurts.

|||

It is often helpful to think about it this way: every noun is a table, so:

Distributor table
Reseller table
Group table
Company table
User tables

A Distributor can have many Resellers
A Reseller can have many Groups
A Group can have many Companies
A Company can have many Users
A User may belong to many Companies

Every time you write, "can have many" it implies the presence of a foreign key (FK). Many-to-many relationships (company-user) requires a cross reference table. So:

Distributor table
- Distributor ID (probably an identity column)
- Reseller ID (FK to Reseller table)
- other fields

Reseller table
- Reseller ID (probably an identity column)
- Group ID (FK to Group table)
- other fields

Group Table
- Group ID (probably an identity column)
- Company ID (FK to Company table)
- other fields

Company Table
- Company ID (probably an identity column)
- other fields

User Table
- User ID (probably an identity column)
- other fields

Company-User-Cross-Reference Table
- Company ID (FK to Company table)
- User Id (FK to User Table)
NOTE: The Primay Key of this table is Company ID + User ID

|||

Hi David, thanks for that!

That does reassure me that I am on the right track with my structure as that is the way I have gone about things.

I think I rushed into it though and ended up creating astructure where a group could belong to multiple resellers which iswrong (and also that resellers could belong to multiple Distributorswhich is also wrong) So I shall remove the cross-reference tables forDistributors and Resellers.

However, I think I need to make the following minor changes to your suggestion:

Group Table
- Group ID (probably an identity column)
- other fields

Company Table
- Company ID (probably an identity column)
- Group ID (FK to Group table)
- other fields

This is so that a Group can have many Companies. (and a Company can only belong to one group)

(And also changes so Reseller Table contains the DistributorID and the Group table contains the ResellerID)

Could you just clear something up for me though please: Youappear to have placed the CompanyID in the Group table - would this notmean that a Group can only have one company? Is it not correct to putthe GroupID in the Company table. Then each Company can be linked to aparticular group?

What do you think..?

Again, thanks for your advice it is greatly appreciated,

Pete

|||

pete_m:

Could you just clear something up for me though please: You appear to have placed the CompanyID in the Group table - would this not mean that a Group can only have one company? Is it not correct to put the GroupID in the Company table. Then each Company can be linked to a particular group?

Yes, you're right, I was doing this too fast. I think you have the idea now.