SQL to get cat ids of posts

  • Resolved deko

    (@deko)


    13 years, 4 months ago

    I trying to get cat ids for posts with SQL outside the loop. The below SQL works, but I need a LEFT JOIN… not sure where…
    SELECT terms.term_id, terms.name, term_taxonomy.description, term_taxonomy.count, term_taxonomy.term_taxonomy_id, posts.ID, term_relationships.object_id, term_relationships.term_taxonomy_id FROM terms INNER JOIN (term_taxonomy INNER JOIN (term_relationships INNER JOIN posts ON posts.ID = term_relationships.object_id) ON term_relationships.term_taxonomy_id = term_relationships.term_taxonomy_id) ON terms.term_id = term_taxonomy.term_id
    suggestions?

Viewing 4 replies - 1 through 4 (of 4 total)
  • Thread Starter deko

    (@deko)

    Actually, that query DOES work. All it needs to a WHERE clause. Still, it’s ugly. Is there a better way?

    Remember, NOT in the loop…

    What if I grab the post ID like this: $postid = $_Get["p"]

    I want to discover what the corresponding category ID is for that $postid.

    Do I have to run a query with 3 joins to find it?

    Thread Starter deko

    (@deko)

    Yes, I do 3 joins…

    $thispost = $_GET["p"];

    "SELECT term_relationships.term_taxonomy_id FROM terms INNER JOIN (term_taxonomy INNER JOIN (term_relationships INNER JOIN posts ON posts.ID = term_relationships.object_id) ON term_relationships.term_taxonomy_id = term_relationships.term_taxonomy_id) ON terms.term_id = term_taxonomy.term_id WHERE (posts.ID = ".$thispost.") LIMIT 1;"

    isn’t normalization great?

    Thread Starter deko

    (@deko)

    actually, 1 join shd do it…

    $thispost = $_GET["p"];
    SELECT term_relationships.term_taxonomy_id FROM term_relationships INNER JOIN posts ON posts.ID = term_relationships.object_id WHERE (posts.ID = ".$thispost.");

    assuming the post is in only one category

    Thread Starter deko

    (@deko)

    corrected…

    $thispost = $_GET["p"];
    SELECT term_taxonomy.term_id FROM term_taxonomy INNER JOIN term_relationships ON term_taxonomy.term_taxonomy_id = term_relationships.term_taxonomy_id INNER JOIN posts ON posts.ID = term_relationships.object_id WHERE (posts.ID = ".$thispost.");

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘SQL to get cat ids of posts’ is closed to new replies.

Tags