CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Naganath
Naganath

Reputation: 31

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

I have two tables:

1) employee with columns: e_id, e_name, address;

2) project with columns: p_id, p_name, start_Date, End_Date.

There is a many-to-many relationship between these two tables.

How can I store this relationship and how can I query for retrieving "Employee details and project details on which he is working"?

Upvotes: 0

Views: 843

Answers (1)

criticalfix
criticalfix

Reputation: 2870

You need a cross reference table inbetween:

EmployeeProjectXref: e_id, p_id

The combination of e_id and p_id can be the primary key for this Xref table.

Then, if @e_id is a variable holding the selected Employee ID:

SELECT E.e_name, P.p_name
FROM EmployeeProjectXref EPX
JOIN Employee E ON E.e_id = EPX.e_id
JOIN Project P ON P.p_id = EPX.p_id
WHERE EPX.e_id = @e_id
ORDER BY P.Name

For more details, add them to the SELECT list. E.g.: SELECT E.e_name, E.address, P.p_name, P.start_date, P.end_date, etc. Here the E is an alias for the Employee table, and P is an alias for the Project table. This query will return one row for each entry in the EmployeeProjectXref table (provided that it references real entries in the Employee and Project tables.) If there is one employee and three projects, you'll get three rows.

Upvotes: 1

Related Questions