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... Link to comment https://forums.phpfreaks.com/topic/165685-counts-on-multiple-table/ 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? Link to comment https://forums.phpfreaks.com/topic/165685-counts-on-multiple-table/#findComment-876108 Share on other sites More sharing options...
rugzo Posted July 18, 2009 Author Share Posted July 18, 2009 Yes, exactly Link to comment https://forums.phpfreaks.com/topic/165685-counts-on-multiple-table/#findComment-877551 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. Link to comment https://forums.phpfreaks.com/topic/165685-counts-on-multiple-table/#findComment-882294 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? Link to comment https://forums.phpfreaks.com/topic/165685-counts-on-multiple-table/#findComment-892273 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. Link to comment https://forums.phpfreaks.com/topic/165685-counts-on-multiple-table/#findComment-894768 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.