Return a field from a table that depends on data of current

[carudy]

Is there a way to return the value from another table that is the field name of the value of the current table.

EXAMPLE -

Table 1:
ID SFTYPE
1 CARPET
2 ALLFLOOR
3 WOOD

Table 2:
ID CARPET ALLFLOOR WOOD
1 500 750 125
2 350 550 75

If I were to lookup ID 1 from table 1, I would want the value of 500 and for 2 I would want the value 550. The SFTYPE is the lookup field for Table 2.

Any assistance would be greatly appreciated. Thanks.

 

[tlbroadbent]

Create a stored procedure with the ID of table 1 as a parameter. In the SP create and execute a dynamic SQL statement.

Create Procedure MyProc @id int As

Declare @sql nvarchar(500)

set nocount on

Select @sql='Select Id, ' + sftype + ' From table2'
From table1
Where Id=@id

exec (@sql)

set nocount off
Go Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[carudy]

Terry:

Thanks for the repsonse. That would work, but I left out one key piece of information or I am not clear of your response. I want to return the results as a resultset.

The results I want are a recordset that looks like this:

ID SF
1 500
2 550

Can I do that lookup on a row by row basis? I received error using a stored procedure within my select statement. The only way that I know to make this work would be using Function in SQL 2000 (so I think, haven't tried). However, I need this to work on SQL 7.

 

[tlbroadbent]

The stored procedure returns a result set. You execute with the following syntax.

Exec MyProc 1

This would return the result set From table2 match ID 1 in table1.

Id Carpet
----------- -----------
1 500
2 350 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[carudy]

Terry:

I am needing to return a diffent column for each row. The column name is the data value.

I would need to return:

Id SF
------- --------
1 500 - lookup Id 1 and SFTYPE = Carpet
2 550 - lookup Id 2 and SFTYPE = Allfloor

For Id 3, I would lookup the Wood column and return that value in the SF field.

Thanks for your help.

 

[tlbroadbent]

You gave an example of your table.
[tt]
Table 1:
ID SFTYPE
1 CARPET
2 ALLFLOOR
3 WOOD

Table 2:
ID CARPET ALLFLOOR WOOD
1 500 750 125
2 350 550 75[/tt]

If you choose ID = 1 from table1 you get 'Carpet.' If you then chose the values in the Carpet column of table2, you find two values 500 (ID=1) and 350 (ID=2). If you only want one value you also need to select an ID in table2. I don't understand what result you seek if this isn't it.

Perhaps you could provide more examples of the results you expect and the selection criteria that that would give those results. Exactly how do you plan to select the records and which columns should appear in the result set for each given criteria?

Thanks, Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[carudy]

Terry:

Here is the actual tables and some of the business issues. This issue is in the Home Builder Industry. These are single family homes.

The first table is a Plan table which includes fields for a floor plan that they offer.

tblPlan
PlanId # Rooms Stories CarpetSF EntrySF KitchenSF
------- ------- ------- -------- ------ ---------
1000 3 1 1700 125 320
1100 4 2 2200 225 350
2200 3 1 1500 90 300

The second table is a list of options that are available in the homes. For example, the buyer can have the option to tile the entry, laminate the entry, or wood the entry. The SfType field is the fild to lookup in the tblPlan table.

tblOption
OptionId Option UnitPrice SfType
-------- ------------------------ --------- ------
100 Low grade carpet 1.25 CarpetSf
101 Middle grade carpet 1.75 CarpetSf
102 High grade carpet 2.25 CarpetSf
200 Tile kitchen upgrade 3.00 KitchenSf
201 Laminate kitchen 2.00 KitchenSf

The answer I am looking for is a price by option by plan.

vwAnswer
OptionId PlanId Quantity UnitPrice
-------- ------ -------- ---------
100 1000 1700 1.25
101 1000 1700 1.75
102 1000 1700 2.25
200 1000 320 3.00
201 1000 320 2.00
100 1100 2200 1.25
101 1100 2200 1.75
...

The field that I am trying to lookup is the Quantity field. That value depends on the SfType value in the tblOption table and the field in the tblPlan. I need to know the size of a particular rooom for that specific plan and for the given option.

 

[carudy]

Terry:

One other note, the database was setup with the Sqft fields in the tblPlan table. Due to the complexity in returning this data, I am working to get the data moved to a new table.

This table would have the following fields: PlanId, SfType, SqFt. This would allow me to perform the necessary lookups with Case Statements.

 

[tlbroadbent]

Let me say that your table structure is not normalized. I propose that tblPlan should be restructured as follows.
[tt]
tblPlan
PlanId # Rooms Stories sfType Quantity
------- ------- ------- -------- --------
1000 3 1 CarpetSF 1700
1100 4 2 CarpetSF 2200
2200 3 1 CarpetSF 1500
1000 3 1 EntrySF 125
1100 4 2 EntrySF 225
2200 3 1 EntrySF 90
1000 3 1 KitchenSF 320
1100 4 2 KitchenSF 350
2200 3 1 KitchenSF 300[/tt]

I would even make sfType an integer data type and create a tblType with two columns, sfType and sfDescription.

I realize that we are sometimes the recipients of poorly designed databases and have no control over them. If you can't create new tales or modify the existing tables then you can create a view like the following. The problem with a view like this is it becomes difficult to maintain and understand as you add more SfTypes.

Create View vwAnswer As

Select ptionID, p.PlanID, p.Quantity, o.UnitPrice
From #tblOption As o Inner Join
(Select PlanID, CarpetSF As Quantity, 'CarpetSf' As sfType From #tblPlan
Union
Select PlanID, EntrySF As Quantity, 'EntrySf' As sfType From #tblPlan
Union
Select PlanID, KitchenSF As Quantity, 'KitchenSf' As sfType From #tblPlan) As p
On o.sfType=p.sfType Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[carudy]

Thank you for your help. I am working with the client to determine the impact on their existing system if we normalize the table. I agree that the view would work, but it would be too difficult to manage. There are more than 30 of these SqFt Types fields.

Thanks again.