Problems in selecting record.

I have a table A which has the following structure:
Column Name(Type)
-- --
ID(int)
Repair Code(varchar)
Damage Code(varchar)
Location Code (varchar)
I also have a the master code table
Column Name(Type)
-- --
Code Type (int)
Code Name (varchar)
Code Description ( varchar)
and have a stored procedure called GetCodeDescription which input the Code
Type and Code Name , display the Code Description found in the code master
table.
I want to select the record from TabelA by inputting a ID , then select the
record which have the follwong structure
Column Name(Type)
--
ID (int)
Repair Code (varchar)
Repair Code Description (varchar)
Damage Code (varchar)
Damage Code Description (varchar)
Location Code (varchar)
Location Code Description (varchar)
How can I do it by using the GetCodeDescription stored procedure?
Thnak you very much !Trying to use the stored procedure is only going to make things much,
much more complicated than necessary. How about just doing a query?
SELECT A.ID,
A.RepairCode, B.COdeDescription as RepairDescription,
A.DamagerCode, C.COdeDescription as DamageDescription,
A.LocationCode, D.COdeDescription as LocationDescription
FROM TableA as A
JOIN MasterCodes as B
ON A.RepairCode = B.CodeName
AND B.CodeType = 1
JOIN MasterCodes as C
ON A.DamageCode = C.CodeName
AND C.CodeType = 2
JOIN MasterCodes as D
ON A.LocationrCode = D.CodeName
AND D.CodeType = 3
Roy
On Tue, 21 Feb 2006 18:41:27 -0800, "BallBall"
<BallBall@.discussions.microsoft.com> wrote:

>I have a table A which has the following structure:
>Column Name(Type)
>-- --
>ID(int)
>Repair Code(varchar)
>Damage Code(varchar)
>Location Code (varchar)
>I also have a the master code table
>Column Name(Type)
>-- --
>Code Type (int)
>Code Name (varchar)
>Code Description ( varchar)
>and have a stored procedure called GetCodeDescription which input the Code
>Type and Code Name , display the Code Description found in the code maste
r
>table.
>I want to select the record from TabelA by inputting a ID , then select the
>record which have the follwong structure
>Column Name(Type)
>--
>ID (int)
>Repair Code (varchar)
>Repair Code Description (varchar)
>Damage Code (varchar)
>Damage Code Description (varchar)
>Location Code (varchar)
>Location Code Description (varchar)
>How can I do it by using the GetCodeDescription stored procedure?
>Thnak you very much !
>|||Thank for the answer , but because my code table container about 12000
records, if i join many times , i think the performance will be affected
"Roy Harvey" wrote:

> Trying to use the stored procedure is only going to make things much,
> much more complicated than necessary. How about just doing a query?
> SELECT A.ID,
> A.RepairCode, B.COdeDescription as RepairDescription,
> A.DamagerCode, C.COdeDescription as DamageDescription,
> A.LocationCode, D.COdeDescription as LocationDescription
> FROM TableA as A
> JOIN MasterCodes as B
> ON A.RepairCode = B.CodeName
> AND B.CodeType = 1
> JOIN MasterCodes as C
> ON A.DamageCode = C.CodeName
> AND C.CodeType = 2
> JOIN MasterCodes as D
> ON A.LocationrCode = D.CodeName
> AND D.CodeType = 3
> Roy
>
> On Tue, 21 Feb 2006 18:41:27 -0800, "BallBall"
> <BallBall@.discussions.microsoft.com> wrote:
>
>|||Try the join first. You will probably find the performance is just fine,
and simpler to manage.
"BallBall" <BallBall@.discussions.microsoft.com> wrote in message
news:36B2D0B0-6201-42BE-933B-079D4FCE44DE@.microsoft.com...
> Thank for the answer , but because my code table container about 12000
> records, if i join many times , i think the performance will be affected
> "Roy Harvey" wrote:
>
Code
master
the|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Your personal narrative and pseudo-code are useless.
That would be a horrible design error! Go back to the foundations; a
table is made up of one and only one kind of entity. There is no such
thing as "master code table" in a valid schema.
Next, a data element can be a type or a code but not a "type_code";
read ISO-11179. Next you confuse rows with records and columns with
fields and do not bother to give us table names.
Finally, how do you get enough codes into a VARCHAR(1) column? In a
good design, codes are fixed length and have a validation rule. There
is no such thing as a magical universal "id" in RDBMS; you are not
actually using IDENTITY in an RDBMS, are you'
I have to make a wild guess about the key
CREATE TABLE RepairRequests
(repair_nbr INTEGER NOT NULL,
repair_code INTEGER NOT NULL
REFERENCES Repairs (repair_code)
ON UPDATE CASCADE,
PRIMARY KEY (repair_nbr, repair_code),
damage_code INTEGER NOT NULL
REFERENCES Damages(damage_code)
ON UPDATE CASCADE,
location_code INTEGER NOT NULL
REFERENCES Locations (location_code)
ON UPDATE CASCADE);
CREATE TABLE Repairs
(repair_code INTEGER NOT NULL PRIMARY KEY,
repair_description VARCHAR(20) NOT NULL,
.) ;
CREATE TABLE Damages
(damage_code INTEGER NOT NULL PRIMARY KEY,
damage_description VARCHAR(20) NOT NULL,
.) ;
CREATE TABLE Locations
(location_code INTEGER NOT NULL PRIMARY KEY,
location_description VARCHAR(20) NOT NULL,
.) ;
I have no idea; where is the code for this stored procedure?
I see that you also do not understand what a repeated group is and how
to program in a tiered architecture. This should be done as a simple
query. But assuming that you really do hate RDBMS, how do you handle
a repair request with more or less than two damages on it?|||>Thank for the answer , but because my code table container about 12000
>records, if i join many times , i think the performance will be affected
If performance is bad the table is not indexed correctly. Also it is
not necessary to guess what performance will be, all you have to do is
run the queries in Query Analyzer to see what it is. Easy, quick, and
you can learn a lot.
Twelve thousand rows is really all that large, by the way. With
proper indexing performance should be fine with ten or a hundred times
as many rows.
Roy