MySQL Query for row count between 2 tables

I'm trying to resolve records from one table but correlate it's parent record into a query for the date range.

The relative fields in this case are:
Table A:
RecordID
DateFrom
DateTo
RegionID

Table B:
RecordID
Event
RegionID

My current query is:
select a.column_data, sum(a.occurrences) as countEvents from
	   (
	   select Event as column_data, count(*) as occurrences  from TableB WHERE  `RegionID` = '6'  group by Event
	   ) a
	     group by a.column_data;

Open in new window


Event has common entries so can be counted.
What I need to do is look up RecordID from TableA and get it to search between a date range, say
DateFrom >= '2014-09-01' AND `DateTo` <= '2015-08-31'

Open in new window


Can someone assist me with writing the query to work for what I'm after?
LVL 6
kiwistagAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
select a.recordid, a.regionid,count(b.event) as occurrences from tableA a
Inner join tableB b on b.recordID=a.recordId and b.regionID=a.regionID
Where a.datefrom => '' and a.datefrom<='' group by a.recordId,a.regionId

the two tables have a double relationship you could first look at revordid only and then at the combination.


I am not really sure I understand what it is you are after.
kiwistagAuthor Commented:
Thanks,
From TableB I need outputs of columns Events & Counts to show me the amount of records that exist from Events that are identical and exist between the dates I set. However the data for the dates is in TableA.
arnoldCommented:
The inner join combines the two tables on common terms/columns (relationship) not sure I understand, tableb has events that are the same and you can group on the, as well with count(*) as rowcount

What output are you looking for?
Recordid,regionid,event,count of occurances
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Ray PaseurCommented:
Would we be on firm ground assuming that RegionID matches between both tables?  In other words, if this were only one table, it could look like this:
Table:
RecordID
Event
DateFrom
DateTo
RegionID


Are the DateFrom and DateTo columns DATETIME type?
kiwistagAuthor Commented:
Hi Ray.
Yes the DateFrom and DateTo are DATETIME columns.
Effectively only the DateFrom is used/needed to query against.

Your assumption on values for both tables is correct. On TableA RecordID is simply called ID (inter Key) but the values in both tables are identical.

TableB may have several records against one RecordID, but each record having it's own Event

So in
TableA
RecordID,PersonID,DateFrom,NumEvents,RegionID
32,2217,2015-02-11 00:00:00,3,6
33,4172,2015-02-22 01:00:16,1,4

TableB:
TableB_ID,EventEntry,Event,RecordID,RegionID
2,1,32,Running,6
3,2,32,Swimming,6
4,3,32,Jumping,6
5,1,33,Hockey,4

NumEvents lists total events entered and EventEntry is the count of the Events entered.

Obviously the extra info above is to clarify how the whole thing is used.
arnoldCommented:
Your column headings do not match the data.

Run the join query without the group by with the date restrictions to see the dataset you will be dealing with...
Ray PaseurCommented:
What Arnold said about the column headings.  Please step back and create the SSCCE for us.  We can use that to give you tested and working examples.  In this case, the things that would be most helpful are (1) the CREATE TABLE statements, (2) a few rows of data that we can INSERT into the tables, (3) the desired output from the query.  Armed with that, we can almost always produce good results for you.  Without that, we are just guessing and we are unable to know whether our advice is useful or misguided.
kiwistagAuthor Commented:
Hi Ray.
The initial columns were examples. The data is unfortunately sensitive enough to not post.
The attached Excel spreadsheet should lay it out better.
I hope this makes more sense.

I'll post a .SQL later with the data & tables also if you wish.
QueryExample.xlsx
Ray PaseurCommented:
The data is unfortunately sensitive enough to not post.
Understood, but having a non-confidential test data set is always a good idea!  I'll try to work with the Excel spreadsheet data.  At first glance it looks sufficient to show a solution. :-)
arnoldCommented:
The peculiar part is eventsnum in table A supposed to much the number of rows in tableB
For presonid,regionid?

Unless the above is correct, unless there is another table that has event from tableb some how ....to numevents. In some relationship reference.
Meaning of evententry is one other such column.

Is the create table sensitive as well?
kiwistagAuthor Commented:
SQL dump attached including creates.
New-Project-20150911-1926.sql
Ray PaseurCommented:
Are you open to the idea of reorganizing some of the data in these tables?

I'm not getting quite what I think I should expect here, but please have a look at what's coming out and the code I'm using.  Here is the output:
stdClass Object
(
    [Event] => Jumping
    [kount] => 1
)
stdClass Object
(
    [Event] => Running
    [kount] => 1
)
stdClass Object
(
    [Event] => Swimming
    [kount] => 2
)

Open in new window

Here is the script and data.  I tried to annotate the rows of the Records table to show which would satisfy date, region, and both. Looks like there are two swimmings, one jumping and one running that meet both date and region.  This does not match what you called out in the spreadsheet, so I may be a bit off base.
<?php // demo/temp_kiwistag.php

/**
 * http://www.experts-exchange.com/questions/28712725/MySQL-Query-for-row-count-between-2-tables.html
 */
error_reporting(E_ALL);


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}


// SSCCE SETUP FROM THE QUESTION AT E-E
$sql =
"CREATE TEMPORARY TABLE `Events` (
  `EventID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `EventNo` int(10) unsigned NOT NULL COMMENT 'Number of Record as entered',
  `RecordID` int(10) unsigned NOT NULL COMMENT 'From Record Table',
  `Event` text NOT NULL COMMENT 'Name of Event',
  `RegionID` int(10) unsigned NOT NULL COMMENT 'Same as in Records (linked)',
  PRIMARY KEY (`EventID`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1"
;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

$sql =
"INSERT INTO `Events` (`EventID`,`EventNo`,`RecordID`,`Event`,`RegionID`) VALUES
 (1,1,2,  'Swimming' ,6),
 (2,1,1,  'Jumping'  ,6),
 (3,2,1,  'Running'  ,6),
 (4,3,1,  'Swimming' ,6),
 (5,1,5,  'Running'  ,6),
 (6,1,6,  'Jumping'  ,6),
 (7,1,7,  'Jumping'  ,6),
 (8,2,7,  'Swimming' ,4),
 (9,1,8,  'Running'  ,6),
 (10,2,8, 'Walking'  ,6),
 (11,3,8, 'Jumping'  ,6)"
;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

$sql =
"CREATE TEMPORARY TABLE `Records` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `PersonID` int(10) unsigned NOT NULL,
  `DateFrom` datetime NOT NULL,
  `NumEvents` int(10) unsigned NOT NULL,
  `RegionID` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1"
;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

$sql =
"INSERT INTO `Records` (`ID`,`PersonID`,`DateFrom`,`NumEvents`,`RegionID`) VALUES
 (1,712,  '2015-02-04 11:00:00', 3,6), /* Date */ /* Region */ /* Both */
 (2,514,  '2015-01-12 06:00:12', 1,6), /* Date */ /* Region */ /* Both */
 (3,23,   '2014-06-12 00:00:00', 2,6),            /* Region */
 (4,3454, '2014-11-16 12:00:01', 4,6), /* Date */ /* Region */ /* Both */
 (5,124,  '2014-02-11 00:00:00', 1,6),            /* Region */
 (6,11,   '2014-12-06 11:00:00', 1,3), /* Date */
 (7,789,  '2015-01-01 14:00:00', 2,4), /* Date */
 (8,553,  '2015-09-04 11:00:01', 3,6)"            /* Region */
;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

$sql = "
SELECT

Event,
COUNT(Event)     AS kount

FROM Events, Records
WHERE Records.ID       = Events.RecordID
AND   Records.RegionID = Events.RegionID
AND   Records.RegionID = 6
AND   Records.DateFrom BETWEEN '2014-09-01' AND '2015-08-31'

GROUP BY Event
ORDER BY Event
"
;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
echo '<pre>';
while ($row = $res->fetch_object()) { print_r($row); }

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kiwistagAuthor Commented:
Of course Temporary tables would make more sense as well! I'll give that a try first.
I'm re-checking over the Query as it's complaining of an error when just running the Query in a MYSQL Browser.
kiwistagAuthor Commented:
Once a formatting change was made in the query it worked as anticipated!
Ray PaseurCommented:
The only reason I use temporary tables is my reflexive habit when creating sample test scripts (I have no reason to keep these tables beyond the life of the request) so that should be independent of anything else in the process.  But if you're getting an error and you want us to look into it, it's helpful to tell us what the error message is, what data it identifies, what line of code is in play, things like that.

One last note that might or might not matter -- this SELECT query as written is undoubtedly susceptible to optimization.  You might want to use EXPLAIN SELECT to see what the query engine is doing with that, and see if there is a way to tighten it up.  Depending on the amount of data in the "real world" optimization might be worth the effort.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.