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

what you want is a LEFT OUTER JOIN
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

Thanks for that your brilliant.

Desmond