SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast LobsterMan's Avatar
    Join Date
    Apr 2005
    Location
    Jerusalem, Israel
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select tags with post

    Hi,
    I have 2 tables in my database, one for posts and one for tags, and i want to get all the tags for each post, in the same query calling for the posts.

    here is a simplified version of my structure:

    table 1 'posts'
    =============================
    post_id post_title post_text


    table 2 'tags'
    =============================
    tag_id post_id tag_title tag_text


    I want to write a query that will list the posts, and for each post, will get all the associated tags, sorted by tag_title=tag_text

    Is this even possible?

    Currently each post makes about 6 queries to the database bacause of the tags and their use in different places, so if I can get all this into one query, it'd be awesome.

    Thanks allot in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm not sure i understand why you want to have tag_title equal to tag_text, but here you go...
    Code:
    SELECT posts.post_title 
         , posts.post_text
         , tags.tag_title 
         , tags.tag_text
      FROM posts
    LEFT OUTER
      JOIN tags
        ON tags.post_id = posts.post_id        
    ORDER
        BY posts.post_title
         , CASE WHEN tags.tag_title = tags.tag_text
                THEN 0 ELSE 1 END
         , tags.tag_title
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast LobsterMan's Avatar
    Join Date
    Apr 2005
    Location
    Jerusalem, Israel
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I didn't mean equal, I meant that it will be the way i get it in php, if I fetch the results as an array (the array title is the tag title, and the content is the tag text)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, i have no idea what you just said, but that's not your fault, it's because i don't do php

    try removing the CASE expression from the ORDER BY clause -- does the query return what you want?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast LobsterMan's Avatar
    Join Date
    Apr 2005
    Location
    Jerusalem, Israel
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    Sorry for not explaining myself.
    With the query you suggested, every tag returns a new result row. I can have 1 post with 20 tags, I don't want the post retuned 20 times, just the all the tags that belong to that post be returned as an array within the one and only result row for that post.
    Is that possible?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yup, very possible -- use the GROUP_CONCAT function
    Code:
    SELECT posts.post_title 
         , posts.post_text
         , GROUP_CONCAT(tags.tag_title) AS tag_titles 
         , GROUP_CONCAT(tags.tag_text) AS tag_texts
      FROM posts
    LEFT OUTER
      JOIN tags
        ON tags.post_id = posts.post_id        
    GROUP
        BY posts.post_title
    ORDER
        BY posts.post_title
    note i don't understand your columns, or whether you wanted both the tag titles and tag texts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •