SQL to return Sales Departments where the sales manager is/isn´t a salesman

[jkarlos]

Hi



I need to build a query where I have 2 tables

MANAGERS

DPT_ID | MANAGER

1 | PERSON_ID1
2 | PERSON_ID2

and SALESMEN

DPT_ID | PERSONID

1 | PERSON_ID1
1 | PERSON_ID7
1 | PERSON_ID8
2 | PERSON_ID4
2 | PERSON_ID5
2 | PERSON_ID6

and 2 queries to retrieve departments where the manager is and isn´t a salesman in the department.

For the above data, the first query would return DPT_ID = 1 and for the second DPT_ID = 2

Thanks

[CJ_London]

you would be better to have one table with an additional field to indicate their role - salesperson or manager. If these are the only two roles and a person can only be a salesperson or a manager this could be done with a Boolean field

However what about if a person changes roles/departments, leaves etc. You would really need a 'start date' and 'end date' field as well and store their roles in a different table

[jkarlos]

In fact, I have but not in this format, what I have here is

DPT_ID | MANAGER | SALESMAN

So, if a Dpt. has 10 salesmen the manager repeats 10 times.

The approach above was the first proposal to avoid this repetition. To have the role field I´ll have to transform the table first.

[CJ_London]

think you need more like

tblPersons
personPK
PersonName


tblDepts
DeptPK
DeptName


tblRoles
RolePK
RoleName


tblDeployed
DeployedPK
PersonFK
DeptFK
RoleFK
StartDate (maybe)
EndDate (maybe)

[orange]

I agree with Ajax --redesign and normalize. If you are going to use a relational database product, you should abide by some of the basic rules/principles/concepts of relational database.

Dbms Principles