jream — 2010-01-28T20:18:14-05:00 — #1
Im trying to make "Categories" only show ones that have atleast 1 "Posts" assigned to them. In other words, it won't list a Category if it is empty.
My problem is I have two tables. I have this totally wrong, can anyone give me some advice?
categories AS c
posts as p
COUNT(p.assign_category) > 1
siradrian — 2010-01-28T20:23:19-05:00 — #2
How about something like this?
(Not sure I understand your table structure so bear with me):
FROM categories AS c
JOIN posts AS p
ON p.assign_category = c.cid
HAVING COUNT(p.assign_category) > 1;
mal_curtis — 2010-01-28T20:25:23-05:00 — #3
You can probably do this much simpler with a grouped inner join?
categories AS c, posts as p
c.id = p.cid
GROUP BY c.id
This way it's selecting all categories that have a correlating post row.
webcosmo — 2010-01-28T20:30:39-05:00 — #4
or may be like this
Select id,title from categories c
where exists(select (1) from posts p where assign_category=c.id)
jream — 2010-01-28T21:23:03-05:00 — #5
Ohh I was not doing JOINS because I didn't want to include anything in it, but I see you can do it this way. I am going to try a few of these out Ty
That is exactly perfect!
I would explain why I dont want to inner join the two together but then Ill confuse myself trying to explain it Haha