sql - How to retrieve records having many to many relationship bet two tables? -

sql - How to retrieve records having many to many relationship bet two tables? -

i have 2 tables:

1) employee columns: e_id, e_name, address;

2) project columns: p_id, p_name, start_date, end_date.

there many-to-many relationship between these 2 tables.

how can store relationship , how can query retrieving "employee details , project details on working"?

you need cross reference table inbetween:

employeeprojectxref: e_id, p_id

the combination of e_id , p_id can primary key xref table.

then, if @e_id variable holding selected employee id:

select e.e_name, p.p_name employeeprojectxref epx bring together employee e on e.e_id = epx.e_id bring together project p on p.p_id = epx.p_id epx.e_id = @e_id order p.name

for more details, add together them select list. e.g.: select e.e_name, e.address, p.p_name, p.start_date, p.end_date, etc. here e alias employee table, , p alias project table. query homecoming 1 row each entry in employeeprojectxref table (provided references real entries in employee , project tables.) if there 1 employee , 3 projects, you'll 3 rows.

sql sql-server sql-server-2008 tsql