Complicated M:N relationship with MySQL tables

Question

I have the following tables:

Table: news
fields: uid, title, categories, datetime, hidden, deleted

Table: categories_mn
fields: uid_local, uid_foreign

Table: categories
Fields: uid, parentcategory, name, image

Every news entry can be assigned to several different categories.

What Im trying to achieve is to get the latest 3 news, and show the image of all the categories that this entry is assigned to (and have a Image assigned)

Something like this:

title  | catimages           |
------------------------------
Post 7 | cat1.jpg            |
Post 6 |                     |
Post 5 | cat1.jpg,cat3.jpg   |
------------------------------

This is all I have so far:

SELECT title, categories
FROM news
WHERE deleted = 0 AND hidden = 0 AND
ORDER BY datetime DESC
LIMIT 3;

Im not very experienced with SQL. Please help.

What is the `categories` field? And also what is the `uid_local` and `uid_foreign`? — Hast, Mar 11 '13 at 17:52
The `categories` field is the uid_local of the M:N relationship table. The other field is the uid of the categories table. — Enrique Moreno Tent, Mar 11 '13 at 18:04
@Dbugger Use group_concat if you want to concate your catimages, i.e. if you want to get cat1.jpg,cat3.jpg. See http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field — Mr. Radical, Mar 11 '13 at 18:24
That was indeed the answer, Mr. Radical. Put it in an answer, so I can give you credit :) — Enrique Moreno Tent, Mar 13 '13 at 11:18

Hituptony · Answer 1 · 2013-03-11T17:54:46.253

0

select b.title, b.categories, a.image from categories a
inner join news b
on a.uid=b.uid
WHERE b.deleted = 0 and b.hidden = 0
order by a.datetime desc
limit 3;

edited Mar 11 '13 at 17:54

answered Mar 11 '13 at 17:49

Hituptony

2,662
3
21
43

I unmarked this as solution. This doesn#t work. It should have been obvious from the beginning, since you make no use whatsoever of the mn table – Enrique Moreno Tent Mar 13 '13 at 10:30
No. I executed your query and it "appeared" to be working properly. But after further testing, I realize i was being deceived by proper output, fruit of coincidence. – Enrique Moreno Tent Mar 13 '13 at 12:53
Very good..sorry I couldn't be of more assistance, It seems you have found your answer above ^^. cheers – Hituptony Mar 13 '13 at 12:55

score 0 · Answer 2 · answered Sep 02 '13 at 17:38

I don't think you need the categories column in the news table. I think this query should work:

SELECT 
    news.title, 
    categories.image 
FROM 
    news 
    INNER JOIN categories_mn ON news.uid=categories.uid_local 
    INNER JOIN categories ON categories.uid=categories_mn.uid_foreign 
WHERE 
    news.hidden=0 AND news.deleted=0 
ORDER BY 
    datetime DESC 
LIMIT 3

I'd also rename the columns in categories_mn so that it's clearer which column refers to which table. Maybe uid_news and uid_categories are good names which also help better understand the m:n relation...

Complicated M:N relationship with MySQL tables

2 Answers2