List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 13 2000 12:08am
Subject:Re: Help with a query....
View as plain text  
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
Thread
Help with a query....Leonard Rosenbeck13 Jan
  • Re: Help with a query....Paul DuBois13 Jan