CODEWITHSUNDEEP
sql-server
AAA
AAA

Reputation: 2450

MS SQL Server select statement: display all combinations from two tables

I have two tables in a MS SQL Server database:

Table1

  Emp_ID, First_Name, Last_Name
  1 Joe Smith
  2 Bob Jones

Table2

  Emp_ID, Dept_ID, Status
  1 1   Active
  1 2   NotActive
  1 3   NotActive
  2 1   Active

What I would like to do is create a SQL select statement that displays a row for every employee and department combination along with the status even if the employee has never been in the department (table 2). For the sample data, this should bring back 6 records since there are 2 employees and 3 departments.

Any help would be appreciated!

Thank you

Upvotes: 2

Views: 1006

Answers (2)

Dudi Konfino
Dudi Konfino

Reputation: 1136

SELECT *
FROM table1  T1 FULL OUTER JOIN table2 T2
ON T1.EMP_ID=T2.EMP_ID

Upvotes: 0

sgeddes
sgeddes

Reputation: 62861

If you don't have a departments table, you'll need to create a subquery to get the distinct list of dept_ids to cross join on:

select emp_id, first_name, last_name, dept.dept_id, status
from empl
  cross join (select distinct dept_id from empdept) dept
  left join empdept on empl.emp_id = empdept.empt_id  
                   and dept.dept_id = empdept.dept_id

Upvotes: 2

Related Questions