I am currently developing a database for project tracking. The db can have up to three groups responsible for each project. So I created a respgrps table that has grp1, grp2, grp3, and grpID. The grpID is related to the project tables grp_ID. I also have a groups table that has groupID and group(actual group name). My problem arises when I try and query the information I can only pull up one of the groups at a time because you can only have the groups table related to one field. Basically what I am wanting to do is two fold query by group to find out all of the projects they are currently working on and query by project and see all of the groups responsible. Can I do this with my current table structure if so how. If I cant what can I do so that I can. Attached below is a breakdown of my tables.
respgrps groups projects
grp1 groupid proj_id
grp2 group grp_id
grp3 project
grpid
Any help or suggestions would be beneficial
respgrps groups projects
grp1 groupid proj_id
grp2 group grp_id
grp3 project
grpid
Any help or suggestions would be beneficial