0

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!

migrated from codereview.stackexchange.com Jan 23 '15 at 5:35

This question came from our site for peer programmer code reviews.

  • On Code Review, we would be glad to review code for Drupal. However, Code Review only reviews correctly working code, and your question sounds like it's not working to your satisfaction yet. Therefore, I'll migrate this question to the Drupal Answers site. (Please feel free to post a Code Review question later once you have a working module.) – 200_success Jan 23 '15 at 5:34
  • What version of Drupal are you targeting? – 200_success Jan 23 '15 at 5:34
  • Thanks, sorry. I was directed by another user to post this to code review. I'm targeting Drupal 7 @200_success – Sage Jan 23 '15 at 7:09
  • Ok, using phpmyadmin I was able to see that the results returned are CORRECT, but they are showing the resource names over and over again which is what they should do with this code. That's fine. So now what I need to find out is how to filter the results to only show one row per title. Suggestions anyone? – Sage Jan 27 '15 at 10:44
0

The answer it appears is to use the GROUP BY statement in my SQL right after the WHERE clauses. So, the following:

GROUP BY rn.entity_id, rn.field_resource_name_value

The returns just one entry for each field resource name.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.