تركيب نتيجه دوsellect وinsertآن در يك جدول

[sama552]

سلام
من دوتاselect دريك storeprocedure دارم يكي از select ها اطلاعات دانش آموزان را ميدهد(Idي دانش آموز وidي درس درselect بعدي اطلاعات مطالعه دانش آموز را ميدهدبنابراين اگه دانش آموزي مطالعه نكرده باشد اطلاعات مطالعه اش درselect دوم نمي آيد ولي در اطلاعاتش درselect اول هست
حالا من ميخوام اين دو رو تركيب كنم به اين صورت كه اطلاعات دانش آموز و اطلاعات مطالعه اش در يك جدول insert شود اگر آن دانش آموز اطلاعات مطالعه داشت در يك فيلدمتناظر آن جدول واردشود اگر نه صفر وارد شود
لطفا اگه كسي مي دونه راهنمايي كنه

[Reza_Yarahmadi]

نميدونم درست متوجه شدم يا نه!!
براي تركيب جداول بجاي inner join از outer join استفاده كنيد ، براي صفر وارد كردن در صورت خالي بودن هم از تابع ISNULL استفاده كنيد
چيزي سبيه كد زير

Insert Into TableName
(Field1,
Field2m,
...)
Select
ISNULL(Field_1, 0),
ISNULL(Field_2, ''),
...
From
Table1 t1 Left Outer Join Table2 t2
On
t1.Id = t2.FId

[sama552]

نميدونم درست متوجه شدم يا نه!!
براي تركيب جداول بجاي inner join از outer join استفاده كنيد ، براي صفر وارد كردن در صورت خالي بودن هم از تابع ISNULL استفاده كنيد
چيزي سبيه كد زير

Insert Into TableName
(Field1,
Field2m,
...)
Select
ISNULL(Field_1, 0),
ISNULL(Field_2, ''),
...
From
Table1 t1 Left Outer Join Table2 t2
On
t1.Id = t2.FId


ممنون از راهنماييت
اگه نخوام دوتا select رو جداگانه در جدول نذارم چطوري ميشه تركيب كرد
در ضمن هاست من با table مجازي مشكل داره
اين هم متن كامل sp
ALTER PROCEDURE [sp_ReadPercentage]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Declare @ToDay DateTime
Select @Today = CAST(FLOOR(CAST(GETDATE() AS DECIMAL(12, 5))) AS DATETIME)


(SELECT student.id, student.firstname, student.lastname, parent.PhoneNumber, dbo.Cours.CourseName, COUNT(student.id) AS DutyCount

FROM dbo.student
INNER JOIN dbo.Parent ON dbo.Parent.Student_Id = dbo.Student.Id
INNER JOIN dbo.RelationSTC ON dbo.RelationSTC.Student_Id = dbo.Student.Id
INNER JOIN dbo.RelationStep r ON r.Student_Id = dbo.Student.Id
INNER JOIN dbo.Card ON card.id = r.Card_Id
INNER JOIN dbo.Cours ON card.Course_Id = dbo.Cours.Id
WHERE
dbo.Student.Active = 'True' AND
((r.Step = 5 and CAST(FLOOR(CAST((r.StudyDate) AS DECIMAL(12, 5))) AS DATETIME) + dbo.RelationSTC.StepDay5<=@Today) or
(r.Step = 4 and CAST(FLOOR(CAST((r.StudyDate) AS DECIMAL(12, 5))) AS DATETIME) + dbo.RelationSTC.StepDay4< = @Today) or
(r.Step = 3 AND CAST(FLOOR(CAST((r.StudyDate) AS DECIMAL(12, 5))) AS DATETIME)+ dbo.RelationSTC.StepDay3< = @Today) or
(r.Step = 2 and CAST(FLOOR(CAST((r.StudyDate) AS DECIMAL(12, 5))) AS DATETIME) + dbo.RelationSTC.StepDay2< = @Today) or
(r.Step = 1 and CAST(FLOOR(CAST((r.StudyDate) AS DECIMAL(12, 5))) AS DATETIME) + dbo.RelationSTC.StepDay1< = @Today))
GROUP BY student.id, student.firstname, student.lastname, parent.PhoneNumber, dbo.Cours.CourseName)

(SELECT dbo.StudyLog.Student_Id,dbo.StudyLog.Cours_Id, COUNT(*)AS readed,CAST(FLOOR(CAST((dbo.StudyLog.Date) AS DECIMAL(12, 5))) AS DATETIME)
FROM dbo.StudyLog
WHERE CAST(FLOOR(CAST((dbo.StudyLog.Date) AS DECIMAL(12, 5))) AS DATETIME)=@Today
GROUP BY dbo.StudyLog.Student_Id, dbo.StudyLog.Cours_Id,CAST(FLOOR(CAST((dbo.StudyLo g.Date) AS DECIMAL(12, 5))) AS DATETIME))
END

[Reza_Yarahmadi]

نيازي به جدا كردن دستورات نيست
ميتونيد بصورت زير اين كار رو انجام بديد

ALTER PROCEDURE [sp_ReadPercentage]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Declare @ToDay DateTime
Select @Today = CAST(FLOOR(CAST(GETDATE() AS DECIMAL(12, 5))) AS DATETIME)

Select
*
From
(SELECT
student.id,
student.firstname,
student.lastname,
ISNULL(parent.PhoneNumber, 0),
ISNULL(dbo.Cours.CourseName, ''),
COUNT(student.id) AS DutyCount

FROM
dbo.student INNER JOIN dbo.Parent ON dbo.Parent.Student_Id = dbo.Student.Id
INNER JOIN dbo.RelationSTC ON dbo.RelationSTC.Student_Id = dbo.Student.Id
INNER JOIN dbo.RelationStep r ON r.Student_Id = dbo.Student.Id
INNER JOIN dbo.Card ON card.id = r.Card_Id
INNER JOIN dbo.Cours ON card.Course_Id = dbo.Cours.Id
WHERE
dbo.Student.Active = 'True'
AND
((r.Step = 5 and CAST(FLOOR(CAST((r.StudyDate) AS DECIMAL(12, 5))) AS DATETIME) + dbo.RelationSTC.StepDay5<=@Today)
OR
(r.Step = 4 and CAST(FLOOR(CAST((r.StudyDate) AS DECIMAL(12, 5))) AS DATETIME) + dbo.RelationSTC.StepDay4< = @Today)
OR
(r.Step = 3 AND CAST(FLOOR(CAST((r.StudyDate) AS DECIMAL(12, 5))) AS DATETIME)+ dbo.RelationSTC.StepDay3< = @Today)
OR
(r.Step = 2 and CAST(FLOOR(CAST((r.StudyDate) AS DECIMAL(12, 5))) AS DATETIME) + dbo.RelationSTC.StepDay2< = @Today)
OR
(r.Step = 1 and CAST(FLOOR(CAST((r.StudyDate) AS DECIMAL(12, 5))) AS DATETIME) + dbo.RelationSTC.StepDay1< = @Today))
GROUP BY
student.id,
student.firstname,
student.lastname,
parent.PhoneNumber,
dbo.Cours.CourseName) as M
INNER JOIN
(SELECT
dbo.StudyLog.Student_Id,
dbo.StudyLog.Cours_Id,
COUNT(*)AS readed,
CAST(FLOOR(CAST((dbo.StudyLog.Date) AS DECIMAL(12, 5))) AS DATETIME)
FROM
dbo.StudyLog
WHERE
CAST(FLOOR(CAST((dbo.StudyLog.Date) AS DECIMAL(12, 5))) AS DATETIME) = @Today
GROUP BY
dbo.StudyLog.Student_Id,
dbo.StudyLog.Cours_Id,
CAST(FLOOR(CAST((dbo.StudyLog.Date) AS DECIMAL(12, 5))) AS DATETIME)) as S
ON
M.ID = S.Student_Id
END

[sama552]

نيازي به جدا كردن دستورات نيست
ميتونيد بصورت زير اين كار رو انجام بديد

ALTER PROCEDURE [sp_ReadPercentage]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Declare @ToDay DateTime
Select @Today = CAST(FLOOR(CAST(GETDATE() AS DECIMAL(12, 5))) AS DATETIME)

Select
*
From
(SELECT
student.id,
student.firstname,
student.lastname,
ISNULL(parent.PhoneNumber, 0),
ISNULL(dbo.Cours.CourseName, ''),
COUNT(student.id) AS DutyCount

FROM
dbo.student INNER JOIN dbo.Parent ON dbo.Parent.Student_Id = dbo.Student.Id
INNER JOIN dbo.RelationSTC ON dbo.RelationSTC.Student_Id = dbo.Student.Id
INNER JOIN dbo.RelationStep r ON r.Student_Id = dbo.Student.Id
INNER JOIN dbo.Card ON card.id = r.Card_Id
INNER JOIN dbo.Cours ON card.Course_Id = dbo.Cours.Id
WHERE
dbo.Student.Active = 'True'
AND
((r.Step = 5 and CAST(FLOOR(CAST((r.StudyDate) AS DECIMAL(12, 5))) AS DATETIME) + dbo.RelationSTC.StepDay5<=@Today)
OR
(r.Step = 4 and CAST(FLOOR(CAST((r.StudyDate) AS DECIMAL(12, 5))) AS DATETIME) + dbo.RelationSTC.StepDay4< = @Today)
OR
(r.Step = 3 AND CAST(FLOOR(CAST((r.StudyDate) AS DECIMAL(12, 5))) AS DATETIME)+ dbo.RelationSTC.StepDay3< = @Today)
OR
(r.Step = 2 and CAST(FLOOR(CAST((r.StudyDate) AS DECIMAL(12, 5))) AS DATETIME) + dbo.RelationSTC.StepDay2< = @Today)
OR
(r.Step = 1 and CAST(FLOOR(CAST((r.StudyDate) AS DECIMAL(12, 5))) AS DATETIME) + dbo.RelationSTC.StepDay1< = @Today))
GROUP BY
student.id,
student.firstname,
student.lastname,
parent.PhoneNumber,
dbo.Cours.CourseName) as M
INNER JOIN
(SELECT
dbo.StudyLog.Student_Id,
dbo.StudyLog.Cours_Id,
COUNT(*)AS readed,
CAST(FLOOR(CAST((dbo.StudyLog.Date) AS DECIMAL(12, 5))) AS DATETIME)
FROM
dbo.StudyLog
WHERE
CAST(FLOOR(CAST((dbo.StudyLog.Date) AS DECIMAL(12, 5))) AS DATETIME) = @Today
GROUP BY
dbo.StudyLog.Student_Id,
dbo.StudyLog.Cours_Id,
CAST(FLOOR(CAST((dbo.StudyLog.Date) AS DECIMAL(12, 5))) AS DATETIME)) as S
ON
M.ID = S.Student_Id
END


ممنون
ولي الان به اين خطا بر خوردم
Msg 8155, Level 16, State 2, Procedure sp_ReadPercentage Line 17
No column was specified for column 4 of 'M'.
Msg 8155, Level 16, State 2, Procedure sp_ReadPercentage, Line 17
No column was specified for column 5 of 'M'.
Msg 8155, Level 16, State 2, Procedure sp_ReadPercentage Line 17
No column was specified for column 4 of 'S'.

[Reza_Yarahmadi]

دوست عزيز اين خطا كه كاملا معلومه به چه خاطره. من دستور رو بصورت مثال نوشتم. براي حلش كافيه بخش زير رو تغيير بديد
ISNULL(parent.PhoneNumber, 0) as PhoneNumber, ISNULL(dbo.Cours.CourseName, '') as CourseName,