good simple example on intersection table to resolve many to many relationship

ewang1205
ewang1205 used Ask the Experts™
on
Could anyone provide a good simple example on intersection table to resolve many to many relationship?

For example I have emp and dept having many to many relationship.  I like to have an intersection table to resolve many to many relationship for query purpose.  Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Naveen KumarProduction Manager / Application Support Manager

Commented:
not sure whether i have got your question well, but then if you dont want releationships meaning joins between tables when querying, then denormaize ( as you said create one table with all the fields required in 1 table with all the fields from both tables emp and dept ).

Thanks
Usually You do it

TABLE EMP (EMPNO, EMPNAME, ...)
TABLE DEPT (DEPTNO, DEPTNAME, ...)
TABLE EMP_DEPT_MAP (EMPNO, DEPTNO, ...)

When querying You simply
select
  from emp
    join emp_dept_map on empno
    join dept on deptno.

I'm not sure if this is what You already know (?) or this is what You are asking for. Definetly this is the most common approach to many-to-many relationships.

Author

Commented:
GGuzdziol:  Yes, those are the three tables.  But, I need sample data to understand how it really works.  Thanks.
EMP
empno empname
1 Jones
2 Smith
3 King
4 Thomas

DEPT
deptno deptname
1 Accounting
2 Marketing
3 Development

Say Jones works for Accounting, Smith for Marketing and Development, King for Accounting and Marketing and Thomas for all of them. So You will get following mapping table:

EMP_DEPT_MAP
empno deptno
1 1
2 2
2 3
3 1
4 1
4 2
4 3

In order to find out which employee works for which department You query:

SELECT emp.empname, dept.deptname
  FROM emp
    INNER JOIN emp_detp_map ON emp.empno = emp_dept_map.empno
    INNER JOIN dept ON dept.deptno = emp_dept_map.deptno;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial