Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: SQL Help with Many to Many Table

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    all employees with or without departments, together with all departments with or without employees, is a full outer join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •