[eluser]Chris Newton[/eluser]
OK. After much MySQL query string testing, this query will do what I need with a few HUGE gotchas.
Code:
SELECT news_table.id AS id, news_table.subject AS subject, news_table.content AS content, news_table.date AS date,
GROUP_CONCAT(news_table.cat_name ORDER BY news_table.cat_name SEPARATOR ',') as catlist
FROM
(SELECT news.id AS id, newscat.name AS cat_name, newscat.id AS cat_id, news.subject AS subject, news.content AS content, news.date AS date
FROM bn_news news
LEFT OUTER JOIN bn_news_to_categories news2cat ON news.id = news2cat.news_id
LEFT OUTER JOIN bn_news_categories newscat ON newscat.id = news2cat.category_id)as news_table
GROUP BY news_table.id
ORDER BY news_table.id
So, what's going on here is that I'm creating a table called news_table in a subquery, and then pulling the data I need from that subquery, and using GROUP_CONCAT to aggregate the many-to-many category items into a text string. This leaves me with the data seen in the attached PNG.
So, with 1 query, I get all of my data, but the categories are now a string, devoid of their accompanying category IDs. I could ALSO pull out the category_IDs into a string list if I chose. So, if I decide to display the category names in the view and make them clickable I have to explode them into an array, and then I'd have to explode the cat_id string into an array too, and hope that they match up. Who knows, maybe there's some mysql command to pull together multiple items into an array or something, but I don't know what it is.
So. Yes, everything in a 3-way many-to-many relationship used for connecting multiple categories to items can be done with 1 query, but I don't (currently) know of a straigthforward method to make those categories very useful. If / when I come up with something else on this, I'll post.
