At 5:58 PM -0500 2000-01-12, Leonard Rosenbeck wrote:
>*NOTE* I'm on the digest version, so if I have left anything out,
>please cc: me so I can reply sooner...
>
>Thanks
>
> I have some tables that I have setup (see create table statements
>below) and am having a hard time with the database structure and
>queries.
>(*NOTE: this is an internal data warehouse for consultans we have
>placed in the past)
>
>--------------------
>create table skilltoconsultant (
> consultantid int not NULL,
> skillid int NOT NULL,
> months int not null
>);
>create table consultants (
> consultantid int NOT NULL primary key,
> consultantname varchar(40),
>);
>create table skills (
> skillid int primary key,
> skilldesc varchar(40)
>);
>---------------------
>
>This works great as long as I am only looking for a single skill and a
>given amount of experience....
>(for instance skillid 2 and more thatn 12 months of experience)
> select c.* from consultants as c, skilltoconsultant as sk where
>c.consultantid=sk.consultantid AND sk.skillid=2 AND sk.months > 12;
>
>---
>
>The problem comes when I want to select all consultants with something
>like this....
> skillid 2 and > 12 months of experience
>AND
> skillid 7 and > 24 months of experience
>
>Does anybody have any suggestions??????
If you're looking for consultants with n skills, you'll need to
join with your skillconcultant table n times. For example:
select c.*
from
consultants as c,
skilltoconsultant as sk,
skilltoconsultant as sk2,
where
c.consultantid=sk.consultantid
AND c.consultantid=sk2.consultantid
AND sk.skillid=2 AND sk.months > 12
AND sk2.skillid=7 AND sk2.months > 24
;
--
Paul DuBois, paul@stripped
