help with a cross table query

[otuatail]

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 have 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.

 

Any ideas on this

 

TIA

 

Desmond.

 

 

[fenway]

You want to get all threads, and just know if a given owner has replied? That's a job for EXISTS.

[otuatail]

What I want is if Jimmy logs on and sees all the current threads, all threads that Jimmy has replied to will be indicated by an icon or symbol against that thread.

 

That's a job for EXISTS

 

If this is the case how do I use it because it is only You EXISTS against this thread id.

 

[otuatail]

Is there any help with this query or is it impossible.

[otuatail]

Ok Problem solved

 

Thanks to http://www.dbforums.com

r937 SQL Consultant 

 

It just needed an extra field created using a case statment.

 

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