CODEWITHSUNDEEP
sql-servermany-to-many
Goolsy
Goolsy

Reputation: 237

Many to Many represented as columns

I have a table representing employees. I have a second table representing various employee profiles, e.g. Employee, Team Leader, Manager, Executive etc. I have a third that maps an employee to a profile in a many-to-many relationship, i.e. an employee can belong to both an 'Employee' profile and a 'Manager' profile.

How can I get a return that shows every employee in my first column and then subsequent columns representing each profile with a Yes-No (or similar) denoting if that employee belongs to each particular profile?

Upvotes: 0

Views: 34

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

You can do this with joins and aggregation:

select e.employee_id,
       max(case when p.profile_name = 'Employee' then 1 else 0 end) as Employee,
       max(case when p.profile_name = 'Manager' then 1 else 0 end) as Manager,
       . . . 
from employees e left outer join
     employee_profile ep
     e.employee_id = ep.employee_id left outer join
     profile p
     on ep.profile_id = p.profile_id
group by e.employee_id;

The data structure itself seems pretty reasonable.

Upvotes: 1

null
null

Reputation: 3517

I think it'd be worthwhile a redesign to remove the many to many that you have.

If they have matching primary/foreign keys you could 

Select * from Table1, Table2 Where Table1ID = Table2ID

Upvotes: 0

Related Questions