Matching data accross 5 tables

[NancyJ]

I have a table of projects
I have a linked table containing the required skills for the project
I have a table of skills
I have a table of users
I have a linked table containing the skills held by users

I want to get a list of projects where all of the project skills can be found within a given users skills

Also a bonus for the other way around, ie. For a given project, find all users with all the skills required for a project.

[barkermn01]

ER not sure whatyour wanting but some thing like the fetch_array would work
E.G
$whanID = (The Id you want);
$query = mysql_query("SLECT * FROM `projects` WHERE`ID`='".$wantID."'");
$rowProjects = mysql_fetch_array($query);
$ProjectID = $rowProjects['ID'];
$query = mysql_query("SELECT * FROM `Needed_Skills` WHERE `Project_ID`='".$projectID."'");

E.G Some thing like that

[NancyJ]

ER not sure whatyour wanting but some thing like the fetch_array would work
E.G
$whanID = (The Id you want);
$query = mysql_query("SLECT * FROM `projects` WHERE`ID`='".$wantID."'");
$rowProjects = mysql_fetch_array($query);
$ProjectID = $rowProjects['ID'];
$query = mysql_query("SELECT * FROM `Needed_Skills` WHERE `Project_ID`='".$projectID."'");

E.G Some thing like that

If I was looking for php solution, I'd have posted in the PHP forum ;) Also, this doesn't actually do what I'm looking for.

[Fumigator]

Can we use subqueries or no?

[NancyJ]

Can we use subqueries or no?

thankfully, for this project, yes! I can't stand that other server.

[bazz]

thankfully, for this project, yes! I can't stand that other server.

sorry to butt in with no help Nancy but, hmph, that went over my head. Does that other server mean the proprietary one and that you are on *nix or am I missing it altogether?

bazz

[NancyJ]

sorry to butt in with no help Nancy but, hmph, that went over my head. Does that other server mean the proprietary one and that you are on *nix or am I missing it altogether?

bazz


One of my clients is on an old mysql server (version 4.0.27) which is really frustrating because, no subqueries makes doing anything even slightly complex either not possible or just clunky and no fun :?(

[NancyJ]

I'm close - this returns all projects where ANY of the project skills match - now to make it return only where ALL skills match.

select * from projects
JOIN project_skills ps1
on projects.id = project_id
where
exists
(select * from user_skills
JOIN project_skills ps2 on
user_skills.skill_id = ps2.skill_id
where user_id = 17 and ps2.project_id = ps1.project_id)
group by project_id

[NancyJ]

Ok, I got it. So for anyone who might have the same problem - here is the answer


SELECT *
FROM projects
JOIN project_skills ps1
ON ps1.project_id = projects.id
WHERE NOT EXISTS (
SELECT *
FROM project_skills AS ps2
WHERE ps2.project_id = ps1.project_id
AND NOT EXISTS (
SELECT us.skill_id
FROM user_skills AS us
WHERE us.skill_id = ps2.skill_id AND us.user_id = 17
))
group by projects.id