Welcome Guest, Not a member yet? Register   Sign In
Help with query: Join, Limit and Count total rows?
#1

[eluser]little brittle[/eluser]
I am having trouble coming up with a query to retrieve blog posts based on abstracted tags. Here is what I want to do:

- Return posts.* tagged with "motorcycles", LIMIT 20
- Return users.username for each post by joining 'users.id' with 'posts.userid'
- Return number of total possible unique posts that match "motorcycles", either with CI query caching, or with SQL_CALC_FOUND_ROWS.

Here is some sample data from my tables. Could someone help guide me in creating a query to pull the right results?
Code:
TABLE "posts"

id  title             content          time         userid
--  --------          -----------      ----         ------
12  my blog title 1   bla bla bla...   1218826136   5
13  my blog title 2   bla bla bla...   1218826136   28
14  my blog title 3   bla bla bla...   1218826136   3


TABLE "tags"

id  name
--  -----
45  cars  
46  trucks  
47  motorcycles


TABLE "tagmap"

id   tag_id   post_id
--   ------   -------
156  46       12
157  46       14
158  45       12
159  47       13

TABLE "users"

id   username
--   --------
3  Frank
4  Jimbo5000
5  wayne


Messages In This Thread
Help with query: Join, Limit and Count total rows? - by El Forum - 11-17-2008, 09:46 PM
Help with query: Join, Limit and Count total rows? - by El Forum - 11-17-2008, 10:38 PM
Help with query: Join, Limit and Count total rows? - by El Forum - 11-17-2008, 10:52 PM
Help with query: Join, Limit and Count total rows? - by El Forum - 11-18-2008, 12:49 AM
Help with query: Join, Limit and Count total rows? - by El Forum - 11-18-2008, 01:15 AM
Help with query: Join, Limit and Count total rows? - by El Forum - 11-18-2008, 01:37 AM



Theme © iAndrew 2016 - Forum software by © MyBB