Joining 4 Tables

ASPnoob

Hi all, suppose I have an app that allows users to search for people based on location, degree held, skills, and experience. How would I join the following tables to achieve what I need:

Person Table
PersonID -----Pk
First Name
Last Name
City
State

Education Table
EducationID --PK
PersonID -----FK
Degree

Skills Table
SkillID ------PK
PersonID -----FK
Skill

JobHistory Table
HistoryID ----PK
PersonID -----FK
Experience

Thanks in advance for your help.

Mycroft Holmes

As your joins are many to one (History to Person) you are going to need a number of queries and/or views to get all the different results. I would create view(s) that joined all the tables, using inner joins where where the FK is required and left joins where the FK is optional and then search that view. Alternatively use joins via the PersonID FK.

Never underestimate the power of human stupidity RAH

Blue_Boy

select p.* ,e.*,s.*,jh.*
from persons p
inner join Education e on e.PersonID = p.PersonID
inner join Skills s on s.PersonID = p.PersonID
inner join JobHistory jh on jh.PersonID = p.PersonID

I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.

ASPnoob

This looks great but what about the conditions? For instance I want to find someone who meets the following conditions:

Education = Masters
Skills = Computer Networking
Experience = 6 years

Also I would like the search to match lower case spelling of Masters and Computer Networking, how are those 2 things I've just mentioned achieved. Thanks for your help.

Mycroft Holmes

Add a where clause with the conditions and user LOWER(Education) = 'masters' You are screwed with the 6 Years, this should have been stored as a numeric and then you could use Duration > 6 You may also want to look into SOUNDEX filtering for the text fields

Never underestimate the power of human stupidity RAH

Lost User

Select TblA.*,TblB.*,TblC.*,TblD.* from persons TblA
inner join Education TblB on TblB.PersonID = TblA.PersonID
inner join Skills TblC on TblC.PersonID = TblA.PersonID
inner join JobHistory TblD on TblD.PersonID = TblA.PersonID