如何在SQL Server中交叉连接两个表

HI I have a table EMP_SCHED which contains COMMENTS Column for EMP_ID and another table SHIFT which also contains COMMENTS column for EMP_ID.Now for a given date range I need to pick COMMNETS from both tables .

我有一个表EMP_SCHED,其中包含EMP_ID的COMMENTS列和另一个表SHIFT,它还包含EMP_ID.Now的COMMENTS列,用于给定日期范围我需要从两个表中选择COMMNETS。

For example in I have 7 EMP_IDs in EMP_SCHED table and 2 EMPIDs in SHIFT .I need all the EMPIDS and COMMNETNS and place the comments for common EMPID from both tables in single row

例如,我在EMP_SCHED表中有7个EMP_ID,在SHIFT中有2个EMPID。我需要所有EMPIDS和COMMNETNS,并将来自两个表的公共EMPID的注释放在一行中

I thought of place join first on EMP_SCHED then on SHIFT but they returned multiple rows

我想到了首先在EMP_SCHED上放置连接然后在SHIFT上,但它们返回了多行

   SELECT distinct E.[EMP_ID]as SCHED_EMP,S.EMP_ID as SHIFT_EMP,
          E.[SCHED_COMMENT],S.COMMENTS     
  FROM [EMP_SCHED_COMMENT] E, SHIFT S
  where e.emp_id =*s.emp_id

The output looked

输出看了

NULL    33018   NULL    Truck Delivery
NULL    33029   NULL    Order Beer/Wine
NULL    33067   NULL    Deli Shift Available
NULL    33115   NULL    Clean backroom
NULL    34232   NULL    ccccc
34020   34020       kkkkkk
34020   34020       looo

Am I doing correct here ? Also MY SQL SERVER is not allowing keywords LFET RIGHT when used for joins

我在这里做得对吗?此外,当用于连接时,MY SQL SERVER不允许关键字LFET RIGHT

(An expression of non-boolean type specified in a context where a condition is expected, near 'LEFT')

(在预期条件的上下文中指定的非布尔类型的表达式,在'LEFT'附近)

2 个解决方案

#1

first get a distinct list of the ids

首先得到一个明确的ID列表

SELECT DISTINCT EMP_ID
FROM EMP_SCHED
UNION
SELECT DISTINCT EMP_ID
FROM SHIFT

then link the original tables.

然后链接原始表。

How can you not use LEFT JOINS?

你怎么能不使用LEFT JOINS?

This works in sql server 2005

这适用于sql server 2005

DECLARE @Employee TABLE(
        EmpID INT,
        Comment VARCHAR(MAX)
)

DECLARE @Shift TABLE(
        Emp_ID INT,
        Comment VARCHAR(MAX)
)

INSERT INTO @Employee (EmpID,Comment) SELECT 1, 'Emp1'
INSERT INTO @Employee (EmpID,Comment) SELECT 2, 'Emp2'
INSERT INTO @Employee (EmpID,Comment) SELECT 3, 'Emp3'

INSERT INTO @Shift (Emp_ID,Comment) SELECT 2, 'Shift2'
INSERT INTO @Shift (Emp_ID,Comment) SELECT 3, 'Shift3'
INSERT INTO @Shift (Emp_ID,Comment) SELECT 4, 'Shift4'

SELECT  *
FROM    (
            SELECT  DISTINCT 
                    EmpID
            FROM    @Employee
            UNION
            SELECT  DISTINCT
                    Emp_ID
            FROM    @Shift
        ) EmpIDs LEFT JOIN
        @Employee e ON EmpIDs.EmpID = e.EmpID LEFT JOIN
        @Shift s ON EmpIDs.EmpID = s.Emp_ID

#2

SELECT EMP_SCHED.COMMENTS, SHIFT.COMMENTS 
FROM EMP_SCHED LEFT OUTER JOIN SHIFT ON
EMP_SCHED.EMP_ID = SHIFT.EMP_ID
WHERE 
[(YOUR DATE RANGE FILTER)]

IN SQL Server syntax for cross join is TABLE1 CROSS JOIN TABLE2..

用于交叉连接的IN SQL Server语法是TABLE1 CROSS JOIN TABLE2 ..

AND Left join is infact LEFT OUTER JOIN AND RIGHT OUTER JOIN IS RIGHT OUTER JOIN. I'm not sure LEFT and/or RIGHT is the proper syntax.

AND左连接实际上是LEFT OUTER JOIN,而右外连接是正确的外连接。我不确定LEFT和/或RIGHT是否是正确的语法。

#1