SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Using data from one sub query within another

    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.
    Code MySQL:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code MySQL:
    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`);
    )

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    sorry for the delay in responding

    did you resolve this yet? i forgot, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I havent been able to find a solution to this since my first post, I'm still stuck unfortunately

  6. #6
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I take it by the lack of replies that what I'm trying to do doesn't work and needs to be split up?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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 --
    Code:
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code MySQL:
    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.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    After messing about with your query I added
    Code:
    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?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by goldkiller07 View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Enthusiast
    Join Date
    Jan 2011
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That makes sense.

    Thanks for your help


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
  •