Stuck on a Query

[JREAM]

Greetings earthlings.

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?

SELECT
    c.id, 
    c.title
 
FROM 
    categories AS c
 
WHERE
    (
        SELECT     
            p.assign_category 
 
        FROM 
            posts as p
 
        WHERE 
            COUNT(p.assign_category) > 1
 
    )

[SirAdrian]

How about something like this?

(Not sure I understand your table structure so bear with me):

SELECT c.id
     , c.title
  FROM categories AS c
LEFT OUTER
  JOIN posts AS p
    ON p.assign_category = c.cid
GROUP
    BY c.id
HAVING COUNT(p.assign_category) > 1;

[Mal Curtis]

You can probably do this much simpler with a grouped inner join?

SELECT
    c.id, 
    c.title
 
FROM 
    categories AS c, posts AS p
 
WHERE
c.id = p.cid
GROUP BY c.id

This way it's selecting all categories that have a correlating post row.

[webcosmo]

or may be like this

Select id,title from categories c
where exists(select (1) from posts p where assign_category=c.id)

[JREAM]

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

or may be like this

Select id,title from categories c
where exists(select (1) from posts p where assign_category=c.id)

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