rugzo Posted July 12, 2009 Share Posted July 12, 2009 Hi, i have 2 tables like tableincoming id name posts time 1 john 5 2009-07-01 2 mike 3 2009-07-02 3 john 2 2009-07-03 4 elise 2 2009-07-05 5 stephan 1 2009-07-02 6 john 2 2009-07-08 7 elise 2 2009-07-10 ... if i want to group and count the posts in time order, it works fine. It's like this -> $sqli = mysql_query("SELECT sum(if(posts!='' and time like 2009-7%,1,0)) as donet,name,time from tableincoming group by name") ; But i have another table for outgoing like this --> tableoutgoing id name outposts time 1 mike 5 2009-07-03 2 mike 3 2009-07-02 3 john 2 2009-07-03 4 john 2 2009-07-01 5 stephan 1 2009-07-02 6 john 2 2009-07-02 7 elise 2 2009-07-10 ... This is just a simple example, the original tables are much more complex. But what i am trying to do is to calculate how many incoming and outgoing post they have per name in one query. I tried many ways but it didn't worked. At the end it should look like below: name posts outposts time mike 3 5 2009-july john 4 2 2009-july stephan 3 1 2009-july elise 5 2 2009-july ... i tried several thing like --> $sqli = mysql_query("SELECT tableincoming, sum(if(tableincoming.posts !='' and tableincoming.time like '2009-7%',1,0)) as donet, tableoutgoing.outposts, sum(if(tableoutgoing.outposts='' and tableoutgoing.time like '2009-7%',1,0)) as done from tableincoming left join tableoutgoing on tableoutgoing.name = tableincoming.name group by tableincoming.name"); Can anyone help? Thanks... Quote Link to comment Share on other sites More sharing options...
fenway Posted July 15, 2009 Share Posted July 15, 2009 So you're trying to join those tables base on name and month/year? Quote Link to comment Share on other sites More sharing options...
rugzo Posted July 18, 2009 Author Share Posted July 18, 2009 Yes, exactly Quote Link to comment Share on other sites More sharing options...
fenway Posted July 24, 2009 Share Posted July 24, 2009 Well, I don't see any reference to month/year in your join condition. Quote Link to comment Share on other sites More sharing options...
rugzo Posted August 6, 2009 Author Share Posted August 6, 2009 i thought that since the time section includes the month, it would understand it or it should be a way that mysql should understand that 2009-07-03 is an entry in July and 2009-july also? Quote Link to comment Share on other sites More sharing options...
fenway Posted August 10, 2009 Share Posted August 10, 2009 What you return in your column list has nothing to do with how to mysql finds the matching records -- you need an ON clause. Quote Link to comment 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.