Using data from one sub query within another

[goldkiller07]

Currently I have a query that is getting a list of forum topics from a topics table. I also have a views table which stores when a registered user has viewed each topic.

What I want to do is select the last viewed time from the views table then count how many posts have a time less than that.

Is it possible to do this in 1 query?

This is my attempt but I'm not sure if this is possible. If it is then my Google skills are lacking.

LEFT OUTER
JOIN ( SELECT MAX(time) AS last_viewed
	FROM views
	WHERE user_id = '1'
	GROUP BY
		time ) AS views
ON views.fid = parents.fid
LEFT OUTER
JOIN (SELECT COUNT(pid)
	FROM posts
	WHERE posts.time < views.last_viewed
	GROUP BY
		posts.pid ) AS read_posts
ON read_posts.fid = parents.fid

Any help would be much appreciated.
Thanks

[r937]

that's not gonna work as written

i'm sure that if i understood the tables involed, i could work through the problem with you

could you please do a SHOW CREATE TABLE for each table

[goldkiller07]

With all the query's where this would be applied I'm getting the latest post id, topic id and forum id (lp.tid) as a join from the post table. What I want to do is find out when the user last viewed that topic and count how many posts there were when they last read the topic, that way in PHP I can redirect to the right post.

CREATE TABLE IF NOT EXISTS `views` (
  `view_id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `tid` int(11) NOT NULL,
  `fid` int(11) NOT NULL,
  `time` int(11) NOT NULL,
  PRIMARY KEY (`view_id`)
);
 
CREATE TABLE IF NOT EXISTS `forum` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`fid`)
);
 
CREATE TABLE IF NOT EXISTS `posts` (
  `pid` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL DEFAULT '0',
  `time` int(11) NOT NULL DEFAULT '0',
  `active` int(11) NOT NULL DEFAULT '1',
  `tid` int(11) NOT NULL DEFAULT '0',
  `fid` int(11) NOT NULL COMMENT 'forum id',
  PRIMARY KEY (`pid`)
);
 
CREATE TABLE IF NOT EXISTS `topics` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(80) NOT NULL DEFAULT '',
  `fid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`tid`);
)

[r937]

sorry for the delay in responding

did you resolve this yet? i forgot, sorry

[goldkiller07]

I havent been able to find a solution to this since my first post, I'm still stuck unfortunately

[goldkiller07]

I take it by the lack of replies that what I'm trying to do doesn't work and needs to be split up?

[r937]

it's tricky and requires careful thought, the time for which was in short supply around here until tonight, but the thunder/heat game hasn't really got going, so i worked it out

you said you wanted to "count how many posts there were when they last read the topic" so please test this --

SELECT COUNT(*) AS posts_read
  FROM ( SELECT MAX(time) AS latest_view_time
           FROM views 
          WHERE uid = $uid -- selected user
       ) AS v
INNER
  JOIN views
    ON views.time = v.latest_view_time
   AND views.uid = $uid -- selected user
INNER
  JOIN posts 
    ON posts.tid = views.tid
   AND posts.time <= views.time

i trust you have a test database comprehensive enough to test this properly?

[goldkiller07]

I've tried running the above query, I'm not really too sure what it is actually outputting, but it shows "2".

What I'm struggling to understand is how that query could be linked together with my main query where it lists the topics for each forum.

For example:

SELECT topics.title
, topics.title
, topics.tid
, forums.name AS `forum_name`
FROM topics
	INNER
	   JOIN forums 
		ON forums.fid = topics.fid
WHERE
	topics.fid = 5
LIMIT 25;

Thanks for your help so far.

[r937]

SELECT forums.name AS `forum_name`
     , topics.title
     , topics.title
     , topics.tid
     , COUNT(*) AS posts_read
  FROM forums
INNER
  JOIN topics 
    ON topics.fid = forums.fid
INNER
  JOIN ( SELECT tid
              , MAX(time) AS latest_view_time
           FROM views 
          WHERE uid = $uid -- selected user
         GROUP
             BY tid ) AS v
INNER
  JOIN views
    ON views.tid = v.tid
   AND views.time = v.latest_view_time
   AND views.uid = $uid -- selected user
INNER
  JOIN posts 
    ON posts.tid = views.tid
   AND posts.time <= views.time
 WHERE forums.fid = 5
GROUP
    BY topics.tid

[goldkiller07]

When I run that query it says all topics have 102 read posts rather than splitting them topic by topic.

I presume it's to do with the GROUP BY function?

[r937]

i had a lot of trouble writing that query, because i can't see half of the problem -- the data

the GROUP BY is supposed to count the posts by topic -- which is what you want, right?

specifically, count the posts that the given user has already seen

[goldkiller07]

After messing about with your query I added

ON v.tid = topics.tid

to the INNER JOIN which seems to have worked.

One thing I did notice was that your query was selecting from "forums" rather than from "topics", is that how I should be doing it or was it a mistake?

[r937]

One thing I did notice was that your query was selecting from "forums" rather than from "topics", is that how I should be doing it or was it a mistake?

it's a common misconception that the SELECT operates directly on the FROM clause, specifically on the first table mentioned

if you look at the query in post #8, you're selecting from the forums table too!!

i just prefer to write my FROM clause tables in a sequence that makes sense

in this case, it's "start with forum row 5, then find all matching topics for that forum"

note the WHERE clause condition indicates which table "drives" the retrieval

make sense?

[goldkiller07]

That makes sense.

Thanks for your help