Search In SQL

Search In SQL

Post by sreci.. » Sat, 15 Jan 2000 04:00:00



OK I have a theoretical DB Question...
If I have 2 tables say a skills table and an employee skills table

so its tbl_Skills - a unique list of skills primKY is SkillID.
AND a tbl_EmployeeSkills - with EmployeeID and SkillID as my fields and
primKY.

There is a one to many relation ship from tbl_Skills to
tbl_EmployeeSkills.

I want to construct a SQL statment that will allow me to search for
employee(s) that match the skills i typed in
so in theory i wan to do something like this

(psuedo-SQL)

SELECt * EmployeeID FROM tbl_EmployeeSkills

WHERE the employee's skill names are LIKE the the typed search text.

Ex.

I want ALL Employees who have Computer, Law, Biology, Martialarts skills
(These skills would be typed in some text field)
It will search the DB for employees whos skills are LIKE the multiple
one typed in the text box but they have to match ALL thoses skills they
typed.

how can i construct a StoredProcedure to do this without haveing to
generate some dynamic SQL script in ASP. I'm using SQL server 7.0

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Search In SQL

Post by Ender » Sat, 15 Jan 2000 04:00:00


select e.name, e.skill_id
from emp_skills e, skills s
where s.skill_id = e.skill_id
and   s.skill_id in (1,3,5)

where 1 ,3 5 represents "dbase" "java" "c++". Not too efficient but
should do the job. From there on you can build other queries on top of
that.



> OK I have a theoretical DB Question...
> If I have 2 tables say a skills table and an employee skills table

> so its tbl_Skills - a unique list of skills primKY is SkillID.
> AND a tbl_EmployeeSkills - with EmployeeID and SkillID as my fields
and
> primKY.

> There is a one to many relation ship from tbl_Skills to
> tbl_EmployeeSkills.

> I want to construct a SQL statment that will allow me to search for
> employee(s) that match the skills i typed in
> so in theory i wan to do something like this

> (psuedo-SQL)

> SELECt * EmployeeID FROM tbl_EmployeeSkills

> WHERE the employee's skill names are LIKE the the typed search text.

> Ex.

> I want ALL Employees who have Computer, Law, Biology, Martialarts
skills
> (These skills would be typed in some text field)
> It will search the DB for employees whos skills are LIKE the multiple
> one typed in the text box but they have to match ALL thoses skills
they
> typed.

> how can i construct a StoredProcedure to do this without haveing to
> generate some dynamic SQL script in ASP. I'm using SQL server 7.0

> Sent via Deja.com http://www.deja.com/
> Before you buy.

--
Ender Wiggin

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Search In SQL

Post by Ender » Sat, 15 Jan 2000 04:00:00


Now that I have read the rest (part about all). Here's a solution not
too bad but works. I may have shot overboard a little :)

 select e.name, count(*)
 from emp_skills e, skills s
 where s.skill_id = e.skill_id
 group by e.skill_id
 having count(*) = (
       select max(c.b)
       from
       (
         select e.name, count(*) b
         from emp_skills e, skills s
         where s.skill_id = e.skill_id
         and   s.skill_id in ('nbla','ssks','skks')
         group by e.skill_id
       ) c
  )



> select e.name, e.skill_id
> from emp_skills e, skills s
> where s.skill_id = e.skill_id
> and   s.skill_id in (1,3,5)

> where 1 ,3 5 represents "dbase" "java" "c++". Not too efficient but
> should do the job. From there on you can build other queries on top of
> that.



> > OK I have a theoretical DB Question...
> > If I have 2 tables say a skills table and an employee skills table

> > so its tbl_Skills - a unique list of skills primKY is SkillID.
> > AND a tbl_EmployeeSkills - with EmployeeID and SkillID as my fields
> and
> > primKY.

> > There is a one to many relation ship from tbl_Skills to
> > tbl_EmployeeSkills.

> > I want to construct a SQL statment that will allow me to search for
> > employee(s) that match the skills i typed in
> > so in theory i wan to do something like this

> > (psuedo-SQL)

> > SELECt * EmployeeID FROM tbl_EmployeeSkills

> > WHERE the employee's skill names are LIKE the the typed search text.

> > Ex.

> > I want ALL Employees who have Computer, Law, Biology, Martialarts
> skills
> > (These skills would be typed in some text field)
> > It will search the DB for employees whos skills are LIKE the
multiple
> > one typed in the text box but they have to match ALL thoses skills
> they
> > typed.

> > how can i construct a StoredProcedure to do this without haveing to
> > generate some dynamic SQL script in ASP. I'm using SQL server 7.0

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

> --
> Ender Wiggin

> Sent via Deja.com http://www.deja.com/
> Before you buy.

--
Ender Wiggin

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Search In SQL

Post by sreci.. » Sat, 15 Jan 2000 04:00:00


The only problem is that there is one skill name per record...
so i'll have 3 seperate records wheer 1 is computers, 2 is karate, and
3 is dancing.

now i have 2 employees jim and bob

bob has dancing and karate as skills (2 recs in the employeeskills
table) and jim had karate and computers (2 recs in the employeeskills)

now if i typed a search for every one with karate it would return both
guys.

if i type a search for karte, computers it should return jim

and if i typed a search for employees with karate and dancing then it
should return bob.

so the delema is that I am unable to come up with a stored procedure
that would execute such a query.



> Now that I have read the rest (part about all). Here's a solution not
> too bad but works. I may have shot overboard a little :)

>  select e.name, count(*)
>  from emp_skills e, skills s
>  where s.skill_id = e.skill_id
>  group by e.skill_id
>  having count(*) = (
>        select max(c.b)
>        from
>        (
>          select e.name, count(*) b
>          from emp_skills e, skills s
>          where s.skill_id = e.skill_id
>          and   s.skill_id in ('nbla','ssks','skks')
>          group by e.skill_id
>        ) c
>   )



> > select e.name, e.skill_id
> > from emp_skills e, skills s
> > where s.skill_id = e.skill_id
> > and   s.skill_id in (1,3,5)

> > where 1 ,3 5 represents "dbase" "java" "c++". Not too efficient but
> > should do the job. From there on you can build other queries on top
of
> > that.



> > > OK I have a theoretical DB Question...
> > > If I have 2 tables say a skills table and an employee skills table

> > > so its tbl_Skills - a unique list of skills primKY is SkillID.
> > > AND a tbl_EmployeeSkills - with EmployeeID and SkillID as my
fields
> > and
> > > primKY.

> > > There is a one to many relation ship from tbl_Skills to
> > > tbl_EmployeeSkills.

> > > I want to construct a SQL statment that will allow me to search
for
> > > employee(s) that match the skills i typed in
> > > so in theory i wan to do something like this

> > > (psuedo-SQL)

> > > SELECt * EmployeeID FROM tbl_EmployeeSkills

> > > WHERE the employee's skill names are LIKE the the typed search
text.

> > > Ex.

> > > I want ALL Employees who have Computer, Law, Biology, Martialarts
> > skills
> > > (These skills would be typed in some text field)
> > > It will search the DB for employees whos skills are LIKE the
> multiple
> > > one typed in the text box but they have to match ALL thoses skills
> > they
> > > typed.

> > > how can i construct a StoredProcedure to do this without haveing
to
> > > generate some dynamic SQL script in ASP. I'm using SQL server 7.0

> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.

> > --
> > Ender Wiggin

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

> --
> Ender Wiggin

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

Search In SQL

Post by Bob Pfeif » Sat, 15 Jan 2000 04:00:00


This should return the id of each employee who has a record in
tbl_employeeskills for any of the skills listed in the where clause.  The
string in the where clause could be passed in as a parameter.

select distinct
    e.employeeid
from
    tbl_employeeskills as e
join
    tbl_skills as s
on
    e.skillid = s.skillid
where
    s.skilldescription in ('Computer', 'Law', 'Biology', 'Martialarts')

 
 
 

Search In SQL

Post by sreci.. » Sat, 15 Jan 2000 04:00:00


This works as an OR condition...

It will return any employee with
'Computer' OR 'Law' OR 'Biology' OR 'Martialarts'

When I really need it to return and EmployeeId in the
tbl_EmployeeSkills table WHERE Skill Like
'Computer' AND 'Law' AND 'Biology' AND 'Martialarts'

So It has to get a total 4 employeeID's for each employee that has all
4 skills. then get a distinct of those IDs.

record would look like this

tbl_EmployeeSkills

EmpId    SkillID
    1          1
    1          2
    1          3
    2          1
    2          2

And if i said SELECT * FROM tbl_EmployeeSkills WHERE SkillID = 1 AND 2
(I know this will not work but i want you to see what i mean)

it should return

EmpId    SkillID
    1          1
    1          2
    2          1
    2          2

then i want the distinct empId from that.

And if i said SELECT * FROM tbl_EmployeeSkills WHERE SkillID = 1 AND 2
AND 3 Then it should return

tbl_EmployeeSkills

EmpId    SkillID
    1          1
    1          2
    1          3

I need something like this to occur...

Thanks for all the help



Quote:> This should return the id of each employee who has a record in
> tbl_employeeskills for any of the skills listed in the where clause.
The
> string in the where clause could be passed in as a parameter.

> select distinct
>     e.employeeid
> from
>     tbl_employeeskills as e
> join
>     tbl_skills as s
> on
>     e.skillid = s.skillid
> where
>     s.skilldescription in

('Computer', 'Law', 'Biology', 'Martialarts')

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Search In SQL

Post by BPMargoli » Sat, 15 Jan 2000 04:00:00


I have to admit to some confusion as to what exactly you are looking for,
however try the following:

create table tbl_EmployeeSkills
(
 EmpId int,
 SkillId int
)
go

insert into tbl_EmployeeSkills values (1, 1)
insert into tbl_EmployeeSkills values (1, 2)
insert into tbl_EmployeeSkills values (1, 3)

insert into tbl_EmployeeSkills values (2, 1)
insert into tbl_EmployeeSkills values (2, 2)
go

create table #SkillsNeeded
(
 SkillId int
)
go

insert into #SkillsNeeded values (1)
insert into #SkillsNeeded values (2)

SELECT t.*
FROM tbl_EmployeeSkills as t
join #SkillsNeeded as s
  on (t.SkillId = s.SkillId)

select distinct EmpId
from (SELECT t.*
      FROM tbl_EmployeeSkills as t
      join #SkillsNeeded as s
        on (t.SkillId = s.SkillId) ) as x


> OK I have a theoretical DB Question...
> If I have 2 tables say a skills table and an employee skills table

> so its tbl_Skills - a unique list of skills primKY is SkillID.
> AND a tbl_EmployeeSkills - with EmployeeID and SkillID as my fields and
> primKY.

> There is a one to many relation ship from tbl_Skills to
> tbl_EmployeeSkills.

> I want to construct a SQL statment that will allow me to search for
> employee(s) that match the skills i typed in
> so in theory i wan to do something like this

> (psuedo-SQL)

> SELECt * EmployeeID FROM tbl_EmployeeSkills

> WHERE the employee's skill names are LIKE the the typed search text.

> Ex.

> I want ALL Employees who have Computer, Law, Biology, Martialarts skills
> (These skills would be typed in some text field)
> It will search the DB for employees whos skills are LIKE the multiple
> one typed in the text box but they have to match ALL thoses skills they
> typed.

> how can i construct a StoredProcedure to do this without haveing to
> generate some dynamic SQL script in ASP. I'm using SQL server 7.0

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

1. Search tool for boolean searching a SQL Server database

Hi,

I am looking for a standard software tool to do boolean searching on a
SQL Server database. I want to search on multiple keywords and multiple
categories at the same time.

Does anyone know if such a tool exists?

Jeroen Vossen

Sent via Deja.com
http://www.deja.com/

2. Exception at program end

3. Slow SQL searches (VB/SQL/Access)

4. MDX question

5. Accents & full-text search in SQL 7

6. Free developers edition?

7. OCX for search-enabling SQL server

8. 17711-NC-Charlotte-ORACLE-SMALLTALK-SQL-P/A - Smalltalk

9. Searching an SQL Database

10. Searching for SQL Server DTS documentation tool

11. Fulltext search and SQL 7 SP2

12. Full Text Search in SQL Server

13. MS Search Service (sql 7) and Local variables