How do i build this JOIN statement? Having trouble with conditions

Question

Now i JUST started getting into joins, so I'm probably missing some core understanding of it, but here's what I want to do:

I have a table with profiles, and several tables (roles, skills, titles etc) with a profile_id, some unrelated data and a value_id (one-to-one relationship)

The user will input an array of different value_ids, and i want to retrieve the id of all profiles that has a corresponding table with a value_id matching those we retrieve from the user.

Some values in the input are specified to only match against a certain table (roles and industries in this case), and that part is working right now. However what i couldn't do was to check if there's a match in any of the relevant tables.

I have no idea how to style this block of sql. Sorry. Edit: It styled itself. Neat.
In this example i have three values. One (88) is specified to search for a certain role, one is for an industry (128) and the third (73) must match at least one row in a set of tables.

SELECT DISTINCT(profiles.id) as id,  
FROM profiles  
INNER JOIN profile_experience_roles r ON(r.profile_id = profiles.id)  
INNER JOIN profile_experience_industries i ON(i.profile_id = profiles.id)  
INNER JOIN profile_experience_technology t ON(t.profile_id = profiles.id)  
INNER JOIN profile_wanted_roles w ON(w.profile_id = profiles.id)  
INNER JOIN profile_languages l ON(l.profile_id = profiles.id)   
WHERE r.value_id = 88 AND i.value_id = 128 AND  
(r.value_id = 73 OR i.value_id = 73 OR t.value_id = 73 OR w.value_id = 73 OR l.value_id = 73)

The final line is what's causing issues. Any help is appreciated.

Also, I've never posted here before, so I have no idea what im doing.
Thanks

That is and outstanding notation `Edit: It styled itself. Neat.`! You made my evening! :) — Mihai Iorga, Sep 06 '12 at 14:06

score 1 · Accepted Answer · answered Sep 06 '12 at 14:10

Try with

WHERE r.value_id = 88 AND i.value_id = 128 AND 
73 IN (r.value_id, i.value_id, t.value_id, w.value_id, l.value_id)

In your case, you should be able to go with

WHERE r.value_id = 88 AND i.value_id = 128 AND 
73 IN (t.value_id, w.value_id, l.value_id)

Since r.value_id can never be 88 and 73. Same goes with i.value_id can't be 128 and 73.

Note that INNER JOIN demands a connection between all of these tables to fetch any rows.

score 1 · Answer 2 · edited May 23 '17 at 11:56

A good example on the difference in joins is here: What is the difference between "INNER JOIN" and "OUTER JOIN"?

The inner join might be the cause. You might be joining on ids that don't exist and all of the data is not being returned. I also included the change that Robin made about the IN and removing r.value and i.value

Try this:

SELECT 
    DISTINCT(profiles.id) as id,  
FROM profiles  
RIGHT JOIN profile_experience_roles r ON(r.profile_id = profiles.id)  
RIGHT JOIN profile_experience_industries i ON(i.profile_id = profiles.id)  
RIGHT JOIN profile_experience_technology t ON(t.profile_id = profiles.id)  
RIGHT JOIN profile_wanted_roles w ON(w.profile_id = profiles.id)  
INNER JOIN profile_languages l ON(l.profile_id = profiles.id)   
WHERE profiles.id is not null AND r.value_id = 88 AND i.value_id = 128 AND 
    73 IN (t.value_id, w.value_id, l.value_id)

How do i build this JOIN statement? Having trouble with conditions

2 Answers2