Many-to-Many Relationships & Active Record Class

[eluser]Chris Newton[/eluser]
yep. nope. I tried to join everything and either a; ended up with corrupt data (categories being added where they shouldn't be) or b; didn't pull any data if no categories are assigned to an item.

Any ideas?

A:

Code:

SELECT bn_news.id,bn_news.subject,bn_news_categories.id, bn_news_categories.name
FROM bn_news
CROSS JOIN bn_news_categories
LEFT JOIN bn_news_to_categories ON (bn_news_to_categories.news_id = bn_news.id AND bn_news_to_categories.category_id = bn_news_categories.id)

B:

Code:

SELECT bn_news.id,bn_news.subject,bn_news_categories.id, bn_news_categories.name
FROM bn_news
CROSS JOIN bn_news_categories
LEFT JOIN bn_news_to_categories ON (bn_news_to_categories.news_id = bn_news.id AND bn_news_to_categories.category_id = bn_news_categories.id)
WHERE bn_news_to_categories.news_id=bn_news.id