otuatail Posted May 29, 2011 Share Posted May 29, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/237771-help-with-a-cross-table-query/ Share on other sites More sharing options...
fenway Posted May 29, 2011 Share Posted May 29, 2011 You want to get all threads, and just know if a given owner has replied? That's a job for EXISTS. Quote Link to comment https://forums.phpfreaks.com/topic/237771-help-with-a-cross-table-query/#findComment-1221869 Share on other sites More sharing options...
otuatail Posted May 29, 2011 Author Share Posted May 29, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/237771-help-with-a-cross-table-query/#findComment-1221873 Share on other sites More sharing options...
otuatail Posted May 30, 2011 Author Share Posted May 30, 2011 Is there any help with this query or is it impossible. Quote Link to comment https://forums.phpfreaks.com/topic/237771-help-with-a-cross-table-query/#findComment-1222542 Share on other sites More sharing options...
otuatail Posted May 31, 2011 Author Share Posted May 31, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/237771-help-with-a-cross-table-query/#findComment-1223168 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.