Ad

Check If Many-to-Many Relationship Exists

I've read through tens of questions here at Stackoverflow and used Google to my best ability but I just can't figure out how to do this:

I have these two tables:

  1. sites (includes fields site_id and site_name)
  2. countries (includes fields country_id and country_name)

Those tables are in a many-to-many relationship between each other through this table:

j_sites_countries (includes fields accepted_country_id, site_FK and country_FK)

The goal is to check if sites accept customers from a certain country and to only show the sites that do accept customers from that country. So, for example, only show sites that accept customers from United States.

What is the line of code needed to check that a relationship between a site and a country exists? I'm absolutely lost here and would appreciate any help.

I assume it goes something like this but I don't know how to actually make the connection between the tables so that I could show the sites where the country_id matches the site_id:

$query = $wpdb->get_results ("SELECT * FROM j_sites_countries WHERE country_FK = '1'");
Ad

Answer

This is a job for JOIN. Let's walk through it:

I'm going to assume until I'm told otherwise that, in the join table, country_FK indicates which country a site is in, and accepted_country_id is the country that site serves.

EDIT: Upon reflection, and in light of comments above, I think it more logical if country_FK is the country the site serves, and the site's location is a column in table sites. I've revised my examples accordingly on the assumption that accepted_country_id is a synthetic key column which can safely be ignored until it has been dropped.

First, just for the sake of completeness, we'll look up the sites serving one country with a simple filtered SELECT.

SELECT site_FK, country_FK
  FROM j_sites_countries
  WHERE country_FK = 'CA';

(You would of course use whatever form of country ID is in your database.)

Next, we'll write a JOIN query to find the names of the countries served by those sites. (Since the only country selected is Canada, this example is more illustrative than practical, but it will come in handy in a later example.) The table aliases here are just to reduce typing, but they'll come in handy later.

SELECT j.site_FK, c.country_name
  FROM countries c
  JOIN j_sites_countries j
    ON c.country_id = j.country_FK
  WHERE j.country_FK = 'CA';

Now we do the same thing to get site names:

SELECT s.site_name, c.country_name
  FROM countries c
  JOIN j_sites_countries j
    ON c.country_id = j.country_FK
  JOIN sites s
    ON j.site_FK = s.site_id
  WHERE j.country_FK = 'CA';

Finally, just for the heck of it, I'm going to join in the name of the country being served and do a little formatting. (This is where table aliases become necessary, as we're joining countriestwice, and MySQL needs to be able to tell the references apart.) To facilitate this, I assume table sites contains a column country which is the location of the site.

SELECT s.site_name AS "Site", c2.country_name AS "In", c1.country_name AS "Serves"
  FROM countries c1
  JOIN j_sites_countries j
    ON c1.country_id = j.country_FK
  JOIN sites s
    ON j.site_FK = s.site_id
  JOIN countries c2
    ON s.country = c2.country_id
  WHERE j.country_FK = 'CA';

To extend the example, to filter by country served and site type, you use a compound WHERE clause:

SELECT s.site_name AS "Site", c2.country_name AS "In", c1.country_name AS "Serves"
  FROM countries c1
  JOIN j_sites_countries j
    ON c1.country_id = j.country_FK
  JOIN sites s
    ON j.site_FK = s.site_id
  JOIN countries c2
    ON s.country = c2.country_id
  WHERE j.country_FK = 'CA'
    AND s.site_type = 'XYZ';

I hope that not only answers your question, but helps you answer the next one.

Ad
source: stackoverflow.com
Ad