The query is trying to find volunteers where the connectors match and the partner_id is not in the volunteer_partner_permissions.
This is what I have so far:
CREATE TEMPORARY TABLE temp_skills
SELECT volunteer_skills_connector.volunteer_id
FROM partner_skills_connector, volunteer_skills_connector
WHERE
partner_skills_connector.skills_id = volunteer_skills_connector.skill_id
AND partner_skills_connector.partner_id=1
group by volunteer_skills_connector.volunteer_id;
CREATE TEMPORARY TABLE temp_regions
SELECT volunteer_regions_connector.volunteer_id
FROM partners, volunteer_regions_connector
WHERE
partners.id=1
AND partners.region=volunteer_regions_connector.region_id
group by volunteer_regions_connector.volunteer_id;
CREATE TEMPORARY TABLE temp_permissions
SELECT volunteer_id
FROM volunteer_partner_permissions
WHERE partner_id != 1;
CREATE TEMPORARY TABLE temp_connector
SELECT temp_skills.volunteer_id
FROM temp_skills, temp_regions
WHERE temp_skills.volunteer_id=temp_regions.volunteer_id
group by temp_skills.volunteer_id;
select volunteers.first_name, volunteers.last_name, volunteers.profile_image, volunteers.email
FROM volunteers, temp_connector, temp_permissions
WHERE volunteers.id=temp_connector.volunteer_id
AND volunteers.id != temp_permissions.volunteer_id
AND temp_connector.volunteer_id != temp_permissions.volunteer_id
group by volunteers.id
This creates a temporary table showing volunteers with skills that match a partner that need the skills. For this example partner.id=1 is used.
CREATE TEMPORARY TABLE temp_skills
SELECT volunteer_skills_connector.volunteer_id
FROM partner_skills_connector, volunteer_skills_connector
WHERE
partner_skills_connector.skills_id = volunteer_skills_connector.skill_id
AND partner_skills_connector.partner_id=1
GROUP BY volunteer_skills_connector.volunteer_id;
This creates a temporary table showing volunteers with interest in a region that match a partner from that region.
CREATE TEMPORARY TABLE temp_regions
SELECT volunteer_regions_connector.volunteer_id
FROM partners, volunteer_regions_connector
WHERE
partners.id=1
AND partners.region=volunteer_regions_connector.region_id
GROUP BY volunteer_regions_connector.volunteer_id;
This creates a temporary table containing volunteers that already have an association with the partner
CREATE TEMPORARY TABLE temp_permissions
SELECT volunteer_id
FROM volunteer_partner_permissions
WHERE partner_id != 1;
This table combines the first two temporary tables to show volunteers with both an interest in a region and have a relevant skill
CREATE TEMPORARY TABLE temp_connector
SELECT temp_skills.volunteer_id
FROM temp_skills, temp_regions
WHERE temp_skills.volunteer_id=temp_regions.volunteer_id
GROUP BY temp_skills.volunteer_id;
This grabs actual volunteer data from the combined list and weeds out data from temp_permissions. All though the weeding out isn’t working right.
SELECT volunteers.first_name, volunteers.last_name, volunteers.profile_image, volunteers.email
FROM volunteers, temp_connector, temp_permissions
WHERE volunteers.id=temp_connector.volunteer_id
AND volunteers.id != temp_permissions.volunteer_id
AND temp_connector.volunteer_id != temp_permissions.volunteer_id
GROUP BY volunteers.id
/* actual volunteer data from the combined list and weeds out data from temp_permissions. */
SELECT volunteers.first_name
, volunteers.last_name
, volunteers.profile_image
, volunteers.email
FROM volunteers
INNER
JOIN ( /* volunteers with both an interest in a region and have a relevant skill */
SELECT temp_skills.volunteer_id
FROM ( /* volunteers with skills that match a partner that need the skills. */
SELECT vsc.volunteer_id
FROM partner_skills_connector AS psc
INNER
JOIN volunteer_skills_connector AS vsc
ON vsc.skill_id = psc.skills_id
WHERE psc.partner_id=1
GROUP
BY vsc.volunteer_id
) AS temp_skills
INNER
JOIN ( /* volunteers with interest in a region that match a partner from that region. */
SELECT vrc.volunteer_id
FROM partners AS p
INNER
JOIN volunteer_regions_connector AS vrc
ON vrc.region_id = p.region
WHERE p.id=1
GROUP
BY vrc.volunteer_id
) AS temp_regions
ON temp_regions.volunteer_id = temp_skills.volunteer_id
GROUP
BY temp_skills.volunteer_id
) AS temp_connector
ON temp_connector.volunteer_id = volunteers.id
LEFT OUTER
JOIN ( /* volunteers that already have an association with the partner */
SELECT volunteer_id
FROM volunteer_partner_permissions
WHERE [COLOR="Blue"]partner_id = 1[/COLOR]
) AS temp_permissions
ON temp_permissions.volunteer_id = temp_connector.volunteer_id
WHERE temp_connector.volunteer_id IS NULL
i’m not sure the “weeding out” is working, because i don’t understand your data, but everything above the LEFT OUTER JOIN should be working just as before
your temp permissions says “volunteers that already have an association with the partner” but you have partner <> 1 which i understand to be volunteers with a relationship to any other partner except partner 1… so i changed that