How to cross join two tables in SQL server

Question

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 .

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

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

   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

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

Answer 1

first get a distinct list of the ids

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?

This works in 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

Answer 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..

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.