如何编写查询以将记录名称添加到主表中 [英] How to write query to add records name into main table

问题描述

我有一个问题，写我的问题是我想写一个这样的查询



在我的数据库中有一个主表联系人在那有两个字段Category1Code和Category2Code，但我想将这些代码替换为name.There为Category1Code和Category2Code表。



表结构是这样的

联系人



联系ID类别1代码Category2Code FirstName LastName



C000001 C1018 C2025 ABC XYZ < br $>




category1code表看起来像这样



Category1Code CategoryName



C1018 PQR







category2code表



Category2Code Category1Code分类名称

C2001 C1018 TTT





i想要将这些代码替换为其名称进入联系表。

请提前查询

thnx ..



最后我要输出这样的进入联系表



ContactID Category1Code Category2Code FirstName LastName



C000001 PQR TTT ABC XYZ

i have one problem for writing query my problem is that i want write a query like this

in my database there is one main table "Contacts" in that there is two field Category1Code and Category2Code but i want to replace these Code into name.There is table for Category1Code and Category2Code.

table structure is like this
"Contacts"

ContactID Category1Code Category2Code FirstName LastName

C000001 C1018 C2025 ABC XYZ


the category1code table look like this

Category1Code CategoryName

C1018 PQR



the category2code table

Category2Code Category1Code Categoryname
C2001 C1018 TTT


i want replace these code into its name into Contact table.
Please give query
thnx in advance..

Finally i want output like this into Contact table

ContactID Category1Code Category2Code FirstName LastName

C000001 PQR TTT ABC XYZ

推荐答案

alter procedure sp_contacts
	(
		@Category1Code nvarchar(20), 
		@Category2Code nvarchar(20), 
		@FirstName nvarchar(20), 
		@LastName nvarchar(20)
	)
AS 
begin
	Declare @CategoryName1  varchar(20)
	Declare @CategoryName2  varchar(20)
	select @CategoryName1= CategoryName from Category1Code where Category1Code = @Category1Code 
	select @CategoryName2= CategoryName from Category2Code where Category2Code = @Category2Code and Category1Code = @Category1Code
   Insert Into contacts
	(
		Category1Code ,
		Category2Code ,
		FirstName,
		LastName
	)
	values
	(
		@Category1Code ,
		@Category2Code ,
		@FirstName,
		@LastName
	)
	update contacts set 
	Category1Code= @CategoryName1,Category2Code= @CategoryName2 where  Category1Code = @Category1Code and Category2Code = @Category2Code
end 
 
 exec sp_contacts 'C1018', 'C2001', 'ABC','XYZ'

i希望它有所帮助。

i hope it helps.

这篇关于如何编写查询以将记录名称添加到主表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！