SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Stuck on a Query

    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?

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

  2. #2
    SitePoint Addict SirAdrian's Avatar
    Join Date
    Jul 2005
    Location
    Kelowna, BC
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about something like this?

    (Not sure I understand your table structure so bear with me):
    Code:
    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;
    Adrian Schneider - Web Developer

  3. #3
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    You can probably do this much simpler with a grouped inner join?
    Code SQL:
    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.

  4. #4
    SitePoint Wizard webcosmo's Avatar
    Join Date
    Oct 2007
    Location
    Boston, MA
    Posts
    1,480
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    or may be like this

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

  5. #5
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Quote Originally Posted by webcosmo View Post
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •