SQL Help with Many to Many Table

ozzii · #1 (**permalink**) 12-30-11, 05:29

I have three tables with a many to many relationships with tbl_emp_dept as follows:

Code:

tbl_employee
emp_id INT
emp_name  VARCHAR

tbl_emp_dept
emp_id INT
dept_id INT

tbl_department
dept_id INT
dept_name VARCHAR

An employee can be assigned to multiple departments or no departments if they are new.

I want to a list of all employees and the departments they are assigned to. The query should list all employees even if they are not assigned to any departments.
The query should also list all departments even if no employee have been assigned to that department.

I need the results to look something like below:

Code:

emp_name | dept_name   | assigned_to_dept
--------------------------------------
emp_xyz  |  dept_1     |  Y
emp_xyz  |  dept_2     |  Y
emp_xyz  |  dept_3     |  null
emp_abc  |  dept_1     |  Y
emp_abc  |  dept_2     |  null
emp_abc  |  dept_3     |  null

Any help appreciated.

r937 · #2 (**permalink**) 12-30-11, 08:34

all employees with or without departments, together with all departments with or without employees, is a full outer join

ozzii · #3 (**permalink**) 01-03-12, 09:27

There are 3 tables involved here so how would that work?

I've tried doing a full join on all three and it still doesnt work.

r937 · #4 (**permalink**) 01-03-12, 10:07

oh, yeah... sorry

it's a bit more complicated, isn't it

okay, try this --

Code:

SELECT e.emp_id
     , e.emp_name  
     , d.dept_id 
     , d.dept_name
  FROM tbl_employee AS e
LEFT OUTER
  JOIN tbl_emp_dept AS ed
    ON ed.emp_id = e.emp_id  
LEFT OUTER
  JOIN tbl_department AS d
    ON d.dept_id = ed.dept_id 
UNION
SELECT e.emp_id
     , e.emp_name  
     , d.dept_id 
     , d.dept_name
  FROM tbl_department AS d
LEFT OUTER
  JOIN tbl_emp_dept AS ed
    ON ed.dept_id = d.dept_id 
LEFT OUTER
  JOIN tbl_employee AS e
    ON e.emp_id = ed.emp_id

it is important here to use UNION, and not UNION ALL, to eliminate the inner join duplicates

Brett Kaiser · #5 (**permalink**) 01-04-12, 11:18

Don't you want something simple like this?

Code:

CREATE TABLE #tbl_employee (
  emp_id INT
, emp_name  VARCHAR(10))

CREATE TABLE #tbl_department (
  dept_id INT
, dept_name VARCHAR(10))
GO

-- We are assuming this is a Junction Table

CREATE TABLE #tbl_emp_dept (
  emp_id INT
, dept_id INT)
GO


INSERT INTO #tbl_employee  (emp_id, emp_name)
SELECT 1, 'Brett'	UNION ALL	-- Assigned to Dept 1
SELECT 2, 'Pat'		UNION ALL	-- Assigned to Dept 1 & 2
SELECT 3, 'Rudy'				-- Assigned to None

INSERT INTO #tbl_department (dept_id, dept_name)
SELECT 1, 'DBA'		UNION ALL	-- 2 Employees
SELECT 2, 'Exec'	UNION ALL	-- 1 Employee
SELECT 3, 'Pool'				-- No Employees

INSERT INTO #tbl_emp_dept (emp_id,dept_id)
SELECT 1, 1		UNION ALL
SELECT 2, 1		UNION ALL
SELECT 2, 2
GO

	SELECT emp_name, dept_name
	  FROM #tbl_employee e
FULL JOIN #tbl_emp_dept ed
		ON e.emp_id = ed.emp_id
FULL JOIN #tbl_department d
		ON ed.dept_id = d.dept_id
GO

DROP TABLE #tbl_emp_dept
GO

DROP TABLE #tbl_department, #tbl_employee
GO

Code:

emp_name   dept_name
---------- ----------
Brett      DBA
Pat        DBA
Pat        Exec
Rudy       NULL
NULL       Pool

(5 row(s) affected)

Internet Services

Web Development

Digital Marketing

Consumer Tech