Re: Bad design or SQL statment quandary

On Tue, Feb 26, 2002 at 09:53:06PM -0800,
grep <cb(at)onsitetech(dot)com> wrote:
> I have 2 tables: 'people' and 'project'. The 'people' table keeps info on
> the key players in a project. The 'project' table keep the project info
> including the roles the people in 'people' table play. I store the people_id
> in the 'project' table. I am at a loss to pull the names from 'people' table
> with a SELECT on the project table. So my questions are:
> 1. what am missing to complete the SELECT?
> 2. Or is this just bad design on my part? and I should break this info out
> to 'roles' table?
>
> Table 'people'
> --------------
> people_id
> name
> email
>
> Table 'project'
> --------------
> proj_id
> description
> proj_mgr
> lead_devel
> qa_tester
>

While role table might be a better way to do things, depending on the
kinds of questions that you ask and whether at some point you might expect
to find multiple people filling the same role on a project, you can get
what you want now.

select name from people, project where proj_id = 'PROJ_ID_YOU WANT' and
(people_id = proj_mgr or people_id = lead_devel or people_id = qa_tester);