无法使用Join In sql [英] Unable to use Join In sql

问题描述

嗨





我想在存储过程中使用加入SQL Server



1st Table



create table JobCard

（

JobCard_Id int primary key not null，

Machine_Id bigint外键引用Machine（Machine_Id）not null，

Estimated_Date date not null，

Breakdown_Date_Time datetime not null，

Priority_Id tinyint外键引用Priority_Table（Priority_Id）不为空，

created_by_Employee_Id smallint外键引用Employee_Details（Employee_Id）not null，

JobCard_Status tinyint not null， - --1待处理，2处理，3等待批准，4拒绝，5关闭，6取消

评论varchar（5000）

）



第二桌



创建表Job_Card_Approve

（

JobCard_Id int foreign关键参考JobCard（JobCard_Id）不为空，

Employee_Id smallint fore ign key REFERENCES Employee_Details（Employee_Id）not null，

Approve_Date Datetime not null，

Repair_Cost money not null，

）





我创建的程序





创建程序retrive_closed_job_card_analytsis_procedure （@year int）as

SELECT *，DATEPART（yyyy，Breakdown_Date_Time）AS年，DATEPART（mm，Breakdown_Date_Time）AS月，DATEPART（dd，Breakdown_Date_Time）AS Job FROM JobCard，其中job_card_status = 1且YEAR（breakdown_time）= @year

go





我想从第一张桌子和Approve_Date，Repair_Cost获得Breakdown_Date_Time从第二个表然后这个查询SELECT *，DATEPART（yyyy，Breakdown_Date_Time）AS年，DATEPART（mm，Breakdown_Date_Time）AS月，DATEPART（dd，Breakdown_Date_Time）作业日来自JobCard，其中job_card_status = 1和YEAR（breakdown_time）= @year



我怎么做

解决方案

试试：

  SELECT  jc。*，
 DATEPART（yyyy，jc.Breakdown_Date_Time） AS 年，
 DATEPART（mm，jc.Breakdown_Date_Time） AS 月，
 DATEPART（dd，jc.Breakdown_Date_Time ） AS 日
  FROM  JobCard jc 
  JOIN  Job_Card_Status jcs 
  ON  jc.JobCard_Id = jcs.JobCard_Id 
  WHERE  jcs.job_card_status =  1   AND  DATEPART（yyyy，jc.Breakdown_Date_Time）=  @ year  

错误：JonCard_I'd for JobCard_I'd [/编辑]

Hi


I want to use Join in SQL Server in Stored Procedure

1st Table

create table JobCard
(
JobCard_Id int primary key not null,
Machine_Id bigint foreign key references Machine (Machine_Id) not null,
Estimated_Date date not null,
Breakdown_Date_Time datetime not null,
Priority_Id tinyint foreign key references Priority_Table (Priority_Id) not null,
created_by_Employee_Id smallint foreign key references Employee_Details (Employee_Id) not null,
JobCard_Status tinyint not null, ---1 Pending ,2 Processing ,3 Waiting For Approval , 4 Reject , 5 Close , 6 Cancel
Comments varchar(5000)
)

2nd Table

Create Table Job_Card_Approve
(
JobCard_Id int foreign key references JobCard (JobCard_Id) not null,
Employee_Id smallint foreign key REFERENCES Employee_Details(Employee_Id) not null,
Approve_Date Datetime not null,
Repair_Cost money not null,
)


Procedure that I create


create procedure retrive_closed_job_card_analytsis_procedure(@year int) as
SELECT *,DATEPART(yyyy,Breakdown_Date_Time) AS Year,DATEPART(mm,Breakdown_Date_Time) AS Month,DATEPART(dd,Breakdown_Date_Time) AS Day FROM JobCard where job_card_status=1 and YEAR(breakdown_time) = @year
go


I want Breakdown_Date_Time from 1st table and Approve_Date, Repair_Cost from 2nd table then this query SELECT *,DATEPART(yyyy,Breakdown_Date_Time) AS Year,DATEPART(mm,Breakdown_Date_Time) AS Month,DATEPART(dd,Breakdown_Date_Time) AS Day FROM JobCard where job_card_status=1 and YEAR(breakdown_time) = @year

How I do this

解决方案

Try:

SELECT jc.*,
       DATEPART(yyyy,jc.Breakdown_Date_Time) AS Year,
       DATEPART(mm,jc.Breakdown_Date_Time) AS Month,
       DATEPART(dd,jc.Breakdown_Date_Time) AS Day 
FROM JobCard jc
JOIN Job_Card_Status jcs 
ON jc.JobCard_Id = jcs.JobCard_Id
WHERE jcs.job_card_status = 1 AND DATEPART(yyyy,jc.Breakdown_Date_Time) = @year 

[edit] Typo: JonCard_I'd for JobCard_I'd[/edit]

这篇关于无法使用Join In sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！