Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005
    Posts
    3

    Unanswered: lp with a cross table query

    Hi Guys. I need your DBA skills with a query. I have created a news group similar to the one you are using. It is quite advanced now. The users table even stores the last two time a user logs on so it can hi-light any threads (this is what I have called them) since the last time the user visited. In addition to adding a reply to the replies table I also duplicate the user Id an date in the threads table to make the main news group query easier. Ok so it is a small duplication. My problem is has a user participated in a thread. I’ll explain with a cut down of the two tables.

    CREATE TABLE IF NOT EXISTS `Threads` (
    `id` int(11) NOT NULL auto_increment,
    `Thread` varchar(32) default NULL, -- for anti-hacking this is an md5()
    `owner_id` varchar(32) default NULL, -- Owner is also an md5() value
    `CreateDate` datetime default NULL,
    `UpdateDate` datetime default NULL,
    `Subject` varchar(80) default NULL,
    `Content` text,

    CREATE TABLE IF NOT EXISTS `Replies` (
    `id` int(11) NOT NULL auto_increment,
    `thread` varchar(32) default NULL,
    `Submitted` varchar(32) default NULL, -- this is the owner id
    `SubmitDate` datetime default NULL,
    `content` text,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=1;

    Ok so if I now say
    SELECT Threads. owner_id, Threads.Subject, Threads.CreateDate From Threads,Replies WHERE Threads.Thread = Replies.thread and Replies. Submitted = $ME

    This would only show threads that you the user has participated in. This NOT what I want. I want all the threads, but If I could only check the replies table to see if you participated in this subject/thread, I could put a symbol against the thread to indicate that you have participated in this. I would for my simplicity like a true/false or better for me a 0/1 field in the returned query. Is there a way to create an extra dummy field in the result set with either true/false or preferably 0/1

    Any ideas on this

    TIA

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what you want is a LEFT OUTER JOIN
    Code:
    SELECT Threads.owner_id
         , Threads.Subject
         , Threads.CreateDate 
         , CASE WHEN Replies.Submitted IS NULL
                THEN 0 
                ELSE 1 END AS zero_or_one
      FROM Threads
    LEFT OUTER
      JOIN Replies 
        ON Replies.thread = Threads.Thread 
       AND Replies.Submitted = $ME
    when there is no matching row, all columns from the right table are set to null, so i have shown how to translate this into the 0/1 that you wanted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2005
    Posts
    3

    Thanks

    Thanks for that your brilliant.

    Desmond

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •