Make homepage|Add to favorites
 

MYSQL TOOLS
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 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
ON cn2.countryid = c2.id 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.

Comments:

Leave a comment
Information
Members of Guests cannot leave comments.