I need some help with my SQL. I've got the following code but it's returning multiples of each item and I don't understand why. When I only include the first Left join it returns results as anticipated...but when I add a second LEFT join and include the 'OR' in the WHERE part of the query it returns multiples of the same resources names. After my query I explain more about the context.
$result = db_query("
SELECT rn.field_resource_name_value,
rn.entity_id,
el.field_experience_level_value,
rl.field_role_value,
FROM {field_data_field_resource_name} AS rn
LEFT JOIN {field_data_field_experience_level} AS el ON rn.entity_id=el.entity_id
LEFT JOIN {field_data_field_role} AS rl ON rn.entity_id=rl.entity_id
WHERE el.field_experience_level_value='$level' OR rl.field_role_value='$role');
This code is part of a recommendations page where users get resources displayed based on their answers to (we'll say two for now) questions on a form. The questions are radio buttons and ask for their experience level and role. These values are saved in session vars that I've already put in $level and $role respectively so those are values like 'Novice' and 'Writer'.
What I'm trying to accomplish - and what the PHP code that displays results (not shown, but below the query) does - is display a list of resources that match the experience level and role the user selected on their form. Obviously I only want each resource to display once, not five times for each. Also, if a user selected both Novice and Writer, let's say, I'd want any resources returned that had EITHER role set to writer OR exp. level set to novice, not necessarily both (although that's fine too).
I have a resource content type that stores it's values in various tables. So each resource title will store its name in the table field_data_field_resource_name, and the matching experience levels (each resource can have multiple levels - some are good for both Novices and Experienced users for example - these are set as list fields in the resource content type). The matching experience levels are stored in another table (ref. in the sql above) and the matching roles, likewise, are ref. in a separate table. The only thing all of them have in common is the entity_id field which matches them all to the resource name...hance the SQL above.
Pfew. So given all that background. Can some knowledgeable SQL expert advise me as to what is going on here?
Thanks!
