CODEWITHSUNDEEP
sqldatabasems-accessms-access-2007lookup
Frames Catherine White
Frames Catherine White

Reputation: 28212

Lookup from multiple tables

Ok, so I have the database of people. Many people can be members of Many organisations. I have this implimented with a table of Orgs a table of people and a table of membership. And then a lookup listbox in Members for both This is fine.

But some people are members of Sub-organisations. So I have a table of Sub-organisation (1 org has many sub orgs)

Now some people are just members of an organisation without being members of any of it's suborgs. some organisations have no suborgs. ATM I implemented this by having the look up look up from both:

SELECT [Sub-Organisations].[Group Name], Organisations.[Organisation Name] FROM Organisations, [Sub-Organisations];

But this only comes up with options to be part of a suborg. What am I doing wrong?

Upvotes: 1

Views: 271

Answers (2)

Crimsonland
Crimsonland

Reputation: 2204

Select [s].[Group Name], [o].[Organisation Name] 
FROM Orgs o
LEFT OUTER JOIN SubOrgs s ON [s].[Organisation Name] = [o].[Organisation Name] 
LEFT OUTER JOIN Membership m

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89661

SELECT so.[Group Name], o.[Organisation Name]
FROM Organisations AS o
LEFT JOIN [Sub-Organisations] AS so
    so.[Organisation Name] = o.[Organisation Name]
    -- Or whatever your parent child relationship identifier is

Upvotes: 1

Related Questions