Join Problem

Anon

I have three tables
STAFF
RANK
SKILLS

I can join RANK and STAFF together easily, as they both contain Staff_id....i'm filtering this join on location, which is in the STAFF table. So only those staff at a certain location are shown.

I can join RANK and SKILS together easily, as they both contain Skill_id....but it needs to be filtered by location....which is in STAFF

So the next stage is to join this info together. These three tables must be linked before the data is any use to be printed to the screen. As RANK contains all the skills a person has and SKILLS the description for each one.

I'm not sure how to do this.

Is it possible for me to add the first join to an array and use that to filter SKILL.

Help me please....can I do this and how.

Cheers Matt

van__

Hmmm...
Would this be what you're looking for:

$sql = "SELECT * FROM STAFF,RANK,SKILLS WHERE STAFF.location ='$location' and RANK.Staff_id=STAFF.Staff_id and SKILL.Skill_id=RANK.Skill_id ORDER BY location";

Anon

yeah thats what I would be after...can you do that 😮

I'll try it out as soon as I get in this evening....ohh I hope it works

cheers in advance

Anon

Can you do this with as many tables as you wish as long as they inter connect with some common ID.

Cheers Matt 😃

van__

Matt,
You sure can.

Actually, I realize that I forgot to declare which table we were using the location to order by, small detail but it would break your query.

Here's the fix:

$sql = "SELECT * FROM STAFF,RANK,SKILLS WHERE STAFF.location ='$location' and RANK.Staff_id=STAFF.Staff_id and SKILL.Skill_id=RANK.Skill_id ORDER BY STAFF.location";

Anon

Cheers it works like a dream....