Thread: Bad design or SQL statment quandary

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


TIA
--
grep

#!/usr/bin/perl
$_='grep';@a=(g..r,e..p);printf("%c%c%c%c",(y;grep;map;)+70,65,length(join('
;',reverse(split
//,++$_)))+$#a+50,s;mapq;grep\;grep\;grep;+56+y;g;j;+y;r;a;+y;e;p;+y;p;h;);$
_=(split/;/)[0];

On Tue, Feb 26, 2002 at 09:53:06PM -0800, grep <cb@onsitetech.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);