Consider two tables. Specialist table has fields like T_ID,T_Title. Project table has fields like P_ID,P_Title, P_Date.
Now, since a Specialist can function on many projects along with a project can be achieved by many people Specialists. Therefore, as apparent, there's a many to a lot of relationship between your two tables. Break the numerous to a lot of, and make up a new table known as Assignment featuring its the foreign secrets T_ID and P_ID.
This is actually the question: I wish to discover a listing of who a specific specialist (Specialist with T_ID = 1 labored with during the last month (April/2011). For instance if Specialist 1 labored with Tech 2 and three they entitled to the above query result and i'd like tech 2 and Tech 3 T_ID T_title.
The solution can as well be according to two queries linked. Kindly tell me what would be the query for that pointed out problem.
This could find all those who done projects using the specialist that has ID = 10. Maybe:
SELECT t.T_ID, t.T_Name FROM Technician t, Project p, Assignments a WHERE t.T_ID = a.T_ID and p.P_ID = a.P_ID and a.P_ID IN (SELECT assign.P_ID FROM Assignments assign, Projects proj WHERE assign.T_ID = 10 and assign.P_ID = proj.P_ID and (proj.P_Date - getdate() <= 30))
The date is some a guess as I am unsure around the syntax, nevertheless the relaxation should obtain the information you would like.
Give Assignment table a principal key of their own (because composite secrets suck). The 2 ? figures represent the ID from the Specialist that you are asking who labored with. For instance: "Who labored with Specialist 5", the ? could be 5's.
SELECT a1.T_ID FROM Assignment a1 WHERE a1.A_ID IN ( SELECT assignment.A_ID FROM Assignment a2 WHERE a2.T_ID = ? ) AND a1.T_ID != ? ;