Select statement: display all combinations from two tables

Question

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!

use a cross join to generate a Cartesian of all rows from one table to all rows in another SELECT * FROM TableA CROSS JOIN TableB older style syntax would be SELECT * FROM TableA, TableB but I would stick with cross join to follow a more current standard. Join Help: blog.codinghorror.com/a-visual-explanation-of-sql-joins — xQbert
– xQbert, Commented Nov 17, 2014 at 20:28
xQbert - but if I do a cross join how do I get it to display the correct status from table2 when there is a match between emp_id on both tables? — AAA
– AAA, Commented Nov 17, 2014 at 20:30

sgeddes · Accepted Answer · 2014-11-17 20:32:18Z

2

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

SQL Fiddle Demo

answered Nov 17, 2014 at 20:32

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AAA Over a year ago

Thank you!! This is perfect. I appreciate the fiddler demo too!

Dudi Konfino · Accepted Answer · 2014-11-17 20:36:57Z

0

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

answered Nov 17, 2014 at 20:36

Dudi Konfino

1,1242 gold badges13 silver badges24 bronze badges

Collectives™ on Stack Overflow

Select statement: display all combinations from two tables

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related