How to extract many-to-many to many-to-many in one query
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.
<pre><code>SELECT c1.name, n1.news_text, c2.name AS associated_country
FROM country c1
JOIN country_news cn1
ON cn1.countryid = c1.id
JOIN news n1
ON cn1.newsid = n1.id
LEFT JOIN countrynews cn2 -- Find associted countries
ON cn2.newsid = n1.id
LEFT JOIN country c2<br /> ON cn2.countryid = c2.id
c1.short_name = 'us' -- Specified country;