Find the database master to help me to write a Sql.

Picture URL:[ ]
For the master to help, FlowList Key0,.Key9, KeyA, Key1... KeyB association is WavFileList WavID.

How to query all the field to the FlowList table and all of the Key field WavName field corresponding to the WavFileList table?

The following is a list of two -- Tsql script
-- the voice file table name: WavFileList
IF NOT EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='WavFileList')
BEGIN
CREATE TABLE WavFileList
(
WavID bigint primary key identity(1,1),ID/ increment field -- the speech
WavName varchar(50) null,The name -- Speech
WavFileName varchar(150) null,-- the voice file name
ConvertFlag int default(0) not null,Mark /0= for conversion -- Speech format conversion, 1= conversion.
CreateTime datetime default(getDate()) Not null-- creation time
)
END
GO

Process table name: FlowList
IF NOT EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='FlowList')
BEGIN
CREATE TABLE FlowList
(
FlowID bigint primary key identity(1,1),-- process ID increment field
FlowName varchar(50) NULL,Process name
WavID int NULL,- call needs to play voice ID
MaxCallTimes int NULL,Recall the times -- don't answer
MaxTryTimes int NULL,- no call number
MaxPlayTimes int NULL,-- play times
MaxRingSec int NULL,The maximum number of seconds -- ringing
MaxTalkSec int NULL,The maximum number of seconds -- call
RePlayKey varchar(4) NULL,Key: hard of hearing
SMSType int NULL,- text type (-1= does not send text messages, 0= according to user specified key messages, 1= according to the call time texting)
SMSKey varchar(4) NULL,- send text messages to the specified key (0-9) or call duration in seconds
SMSID int NULL,- SMS ID
ServerNO int NULL, -- the server number ID, the default -1 said all of the servers are applicable.
IsUse int default(1) NULL,Whether effective, the default 1=, 0= failure disabled
StartTime datetime, -- began to call time
EndTime datetime,- end call time
CallerNum varchar(20), - calling number
Key0 int NULL,0 - button to play the voice of ID
Key1 int NULL,1 - button to play the voice of ID
Key2 int NULL,2 - button to play the voice of ID
Key3 int NULL,3 - button to play the voice of ID
Key4 int NULL,4 - button to play the voice of ID
Key5 int NULL,5 - button to play the voice of ID
Key6 int NULL,6 - button to play the voice of ID
Key7 int NULL,7 - button to play the voice of ID
Key8 int NULL,8 - button to play the voice of ID
Key9 int NULL,9 - button to play the voice of ID
KeyA int NULL,- button * need play voice ID
KeyB int NULL,- button # need to play voice ID
KeyAny int NULL - button any need to play voice ID
)
END
GO

Started by Rock at November 15, 2016 - 8:34 PM

select a.*,b.WavName,c.WavName,.....
from FlowList a
left join WavFileList b on a.Key0=b.WavID
left join WavFileList c on a.Key1=c.WavID
...

Posted by Jeffery at November 17, 2016 - 8:58 PM

Is that right:
select
FlowID
,FlowName
,WavID
,MaxCallTimes
,MaxTryTimes
,MaxPlayTimes
,MaxRingSec
,MaxTalkSec
,RePlayKey
,SMSType
,SMSKey
,SMSID
,ServerNO
,IsUse
,StartTime
,EndTime
,CallerNum
,(select WavName from WavFileList where WavID = Key0) as WavName1
,(select WavName from WavFileList where WavID = Key1) as WavName2
,(select WavName from WavFileList where WavID = Key2) as WavName3
,(select WavName from WavFileList where WavID = Key3) as WavName4
,(select WavName from WavFileList where WavID = Key4) as WavName5
,(select WavName from WavFileList where WavID = Key5) as WavName6
,(select WavName from WavFileList where WavID = Key6) as WavName7
,(select WavName from WavFileList where WavID = Key7) as WavName8
,(select WavName from WavFileList where WavID = Key8) as WavName9
,(select WavName from WavFileList where WavID = Key9) as WavName1
,(select WavName from WavFileList where WavID = KeyA) as WavNameA
,(select WavName from WavFileList where WavID = KeyB) as WavNameB
,KeyAny
from flowlist

Posted by Ralap at November 26, 2016 - 9:55 PM