We help IT Professionals succeed at work.
Get Started

Join to table specified as string in column and no foreign key

211 Views
Last Modified: 2012-06-27
I have inherited a db which sets out a series of modules to which users may or may not have certain permissions to do things (add, edit, delete) .  These permissions are defined in other tables (called Permission1, Permission2, Permission3) and the names of the tables in which the permissions defined are set out in a column of the modules table.  

My challenge is to create a query so that all the Modules are shown and any permissions to these modules.  The attached spreadsheet shows the simple tables and type of result that I am trying to achieve.

Any help in doing this would be greatly appreciated
rbs

The tables are as follows:
Module				
ModuleId	Module	RelatedTable		
1	Alpha	"Permission1"		
2	Beta			
3	Charlie 	"Permission2"		
4	Delta	"Permission2"		
5	Foxtrot	"Permission3"		
				
Permission1 Table				
UserId	View	Edit	Delete	
1	yes	no	no	
2	no	yes	no	
3	yes	no	no	
				
Permission2				
UserId	View	Edit	Delete	
3	yes	no	no	
1	no	yes	no	
				
				
Permission3				
UserId	View	Edit	Delete	
3	yes	no	no	
4	no	yes	no	
5	yes	no	no	
				
Results of Query to Get Module Permissions where User = 1				
				
ModuleId	Module	View	Edit 	Delete
1	Alpha	yes	no	no
2	Beta	no	yes	no
3	Charlie 	null	null	null
4	Delta	null	null	null
5	Foxtrot	null	null	null

Open in new window

Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2011
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE