Make homepage|Add to favorites

reviews, articles, tips & snippets

Buy SQL » MySQL » Extract many-to-many to many-to-many in one query

Extract many-to-many to many-to-many in one query

Author: rootsql Date: 10-11-2011, 22:09

We have three tables: `country`, `news` and `country_news` that is used to support a many-to-many relationship between news and countries. A country has many news and news can be from many countries.

I want to find all new for specified country, and then I want to select the countries associated with the found news.


SELECT, n1.news_text, AS associated_country
  FROM country c1
JOIN country_news cn1
  ON cn1.countryid =
JOIN news n1
  ON cn1.newsid =
LEFT JOIN countrynews cn2 -- Find associted countries
  ON cn2.newsid =
LEFT JOIN country c2
ON cn2.countryid = WHERE c1.short_name = 'us' -- Specified country;
Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.


Leave a comment
Members of Guests cannot leave comments.