Join tables based on values passed from a table?

[wlfpackr]

I'm attempting to JOIN two tables in SQL Server 2000, but I need to pass a variable somehow from one to the other (or perhaps there is some other method I'm not aware of).

Here is my current JOIN and gives me the results I currently want, but I need to expand it.

SELECT		e.*
FROM		Table1 AS e INNER JOIN Table2 AS p 
		ON e.AccessValue = p.PositionTitle

TABLE1 has 10 fields, but only 2 of value for the problem: AccessValue, AccessType
TABLE2 has about 30 fields.

The new approach I want is to JOIN the table on:

Table1.AccessValue = Table2.<[i]the value from Table1.AccessType[/i]>

I'm just not sure I know where to start to get this to work. I attempted Declaring a variable, but it didn't work

=================
There are 10 kinds of people in this world, those that understand binary and those that do not.

 

[Qik3Coder]

wlfpackr,
Need some clarity on what you have going on.
Your table columns aren't speaking for themselves.

Normally you join tables on ID, or other fields that are going to have the same values in both tables. Your sample code shows AccessValue = PositionTitle, which will probably never have a collision, so you get no rows back.

Try changing your inner join to a left join and return both columns, so that you see what you are getting back.

No variables for something like this.

A lack of experience doesn't prevent you from doing a good job.

 

[wlfpackr]

Sorry, I tried to scale back for simplicity sake, but I went too far.

Table1.AccessValue would have values of: PositionTitle, Department, Division, State, Country. Any value in Table1.AccessValue would equal a field/column heading in Table2 i.e. Table2 would have Table2.PositionTitle, Table2.Division, Table2.Department, Table2.State, etc.

So if the value of Table1.AccessValue = "Department", I would want to join Table1.AccessValue to Table2.Department. If the value of Table2.AccessValue = "State", I would want to join Table1.AccessValue to Table2.State.



=================
There are 10 kinds of people in this world, those that understand binary and those that do not.

 

[gmmastros]

Please be aware that the following query I present is likely to run slow if there are a lot of rows in the tables.

Select  e.*
From    Table1 as e 
        Inner Join Table2 as p
          On e.AccessValue = Case When e.AccessValue = 'PositionTitle' Then p.PositionTitle
                                  When e.AccessValue = 'Department' Then p.Department
                                  When e.AccessValue = 'Division' Then p.Division
                                  End

Alternatively, you could try something like this:

Select  e.*
From    Table1 as e 
        Inner Join Table2 as p
          On e.AccessValue = p.PositionTitle
          And a.AccessValue = 'PositionTitle'

Union All

Select  e.*
From    Table1 as e 
        Inner Join Table2 as p
          On e.AccessValue = p.Department
          And e.AccessValue = 'Department'
       
Union All

Select  e.*
From    Table1 as e 
        Inner Join Table2 as p
          On e.AccessValue = p.Division
          and e.AccessValue = 'Division'

This query is likely to perform better (especially with appropriate indexes, but there is more code to maintain.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

wlfpackr,

I have used something similar to gmmastros' first query, but I have an inkling that you mispoke about about what you need.

I'm assuming you have the AccessValue column and an additional DataValue column and you need something like

a.DataValue = case a.AcessValue
When 'State' then t.State
when 'position' then t.position
...


Otherwise your code doesn't need any complex join logic and it's a straight a = b

HTH,
Lodlaiden



A lack of experience doesn't prevent you from doing a good job.

 

[PWise]

not sure either what op needs pehaps

declare @AccessValue varchar(50)
Select @AccessValue='PositionTitle'
Select  e.*
From    Table1 as e 
        Inner Join Table2 as p
          On e.AccessValue = p.PositionTitle
          And @AccessValue = 'PositionTitle'

Union All

Select  e.*
From    Table1 as e 
        Inner Join Table2 as p
          On e.AccessValue = p.Department
          And @AccessValue = 'Department'
       
Union All

Select  e.*
From    Table1 as e 
        Inner Join Table2 as p
          On e.AccessValue = p.Division
          and @AccessValue= 'Division'