-2
$AcceptEventCount = mysql_query("SELECT COUNT(*) as count, tblDevices.name, tblEvents.sentdeviceid FROM tblDevices,tblEvents WHERE tblDevices.deviceid = tblEvents.sentdeviceid
GROUP BY tblEvents.sentdeviceid ORDER BY count DESC");

$DeclineEventCount = mysql_query("SELECT COUNT(*) as cnt, tblDevices.name, tblDeclinedEvents.deviceid FROM tblDevices,tblDeclinedEvents  WHERE tblDevices.deviceid = tblDeclinedEvents.deviceid
GROUP BY tblDeclinedEvents.deviceid ORDER BY cnt DESC");

I'm new to merging two queries in php mysql. I tried unions with it but i'm not able to get the desired result.

Basically I want to get a count of the accepted events and the declined events of the devices from the table tbldevices. Three tables are involved here. can anyone help me with this?

this is what i tried! 

SELECT COUNT() as count, tblDevices.deviceid,null,tblEvents.sentdeviceid
from tblEvents,tblDevices 
WHERE tblEvents.sentdeviceid = tblDevices.deviceid 
GROUP BY tblEvents.sentdeviceid 

UNION 

SELECT COUNT() as cnt,tblDevices.deviceid,tblDeclinedEvents.deviceid,null 
from tblDevices,tblDeclinedEvents 
WHERE tblDeclinedEvents.deviceid = tblDevices.deviceid 
GROUP BY tblDeclinedEvents.deviceid
Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
Hrishikesh Potdar
  • 35
  • 6
  • 1
    Please show us what you tried, maybe you were very close to the right solution, and we just have to give you a little push further. :) – Jordumus Apr 22 '15 at 15:24
  • so in php $row['count'] im hoping to get the accepted events count and in $row['cnt'] I'm hoping to get the declined events count of devices. – Hrishikesh Potdar Apr 22 '15 at 15:28
  • Please edit your original question with this information :) – Jordumus Apr 22 '15 at 15:31
  • Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Apr 22 '15 at 15:47

1 Answers1

0

I think it is not possible (at least without using sub query). The reason is that group by needs to be done on fields, but accepted and declined events are not identified by a field but by tables.

If you can't modified the table structure, having 2 queries is a reasonable options. If you can change the table structure, I would recommend adding a column that store the validation status.

maalls
  • 749
  • 8
  • 20