Correct SQL Statement for Joining Taxonomy Terms

Question

I have 2 different taxonomy vocabularies setup.

1. Courses (First Aid, H2S Alive, Air Monitoring, Gas Detection, etc...)
2. Locations (Vancouver, Calgary, Toronto, Montreal)

I have a content type called Landing Page for each of the courses that has a taxonomy reference field so you can assign a course or multiple courses to that node.

I have a second content type for Courses which also has the 2 taxonomy vocabularies set as taxonomy reference fields on its nodes. These course nodes get displayed in a calendar format and is setup with the entity registration module.

WHAT I NEED HELP WITH:

Im looking for a way to output a set of buttons on the Landing Page nodes that will give me a the list of locations that have been assigned to any node that is based on the given Courses taxonomy term.

For example;

3 Different Courses nodes all have First Aid assigned to them. However, they have 3 different locations set to them.

I've created a landing page for the First Aid Course assigned it the First Aid taxonomy term. I need to display a set of locations that this course has courses offered at.

Answer 1

If you want to show Locations a Course is offered. Or Courses at a Location I would recommend using the Views Field module, or perhaps Context to show a View on the content type page.

Views field allows you send parameters to the embedded field view from the source Node. So you should be able to get the related content(s) you want to show by passing the necessary filter parameter criteria you want to the View -- usually as a contextual filter.

For example for a Landing Page Content Type (eg, First Aid) you add a field that is a View. Call the view like locations for course. Then make a View that outputs Location taxonomy term names based on a contextual filters. The contextual filter will be:

• the taxonomy term name of the course(s) in your taxonomy of Courses.

Then output the taxonomy Location term names for results of the set course name contextual filter. Overwrite the linkpath as needed.

Answer 2

In views you can add a relationship to emulate an sql join. In the relationship you define the field that you want to use for this join. The cool thing is that a relatioship has a name. So when you add a field to display you can use this relatioship. Eg if you add field_sometag it displays the fields of your base entity. But when select to use the relationship then the field displays info about the joined entity

Answer 3

Alright I finally figured it out with the help of @tenken. I didn't end up needing any extra modules except the Views Distinct module to reduce my duplicates as the regular aggregation and DISTINCT items weren't working (assuming it was due to the fact that I had the relationship in there). I was able to relate it through the relationships and the default views.

Heres how I did it;

And the field settings. Its imperative you use Content: All Taxonomy Terms with the "node" relationship applied to it.

And Finally the Views Distinct Module settings.