[SOLVED] Select Count Query, 2 Tables with identical structures

[grimmier]

I have 2 Tables that have the exact same structures, One is for Inventory, the other is for Relief Inventory (ie. when an item is sold it is moved to relief).

 

What i want to be able to do is to run a query that will count the number of items tested by one person and display that sorted and grouped by the date cleared.

 

I can get this to work when working with just the Inventory table, but the problem is that as items are moved out to relief the counts will change.

 

$query = "SELECT COUNT( * ) AS `Count` , `Date Cleared` , `Tested By` FROM `Inventory` WHERE `Tested By` = ".'"'.$tester.'"'." GROUP BY `Date Cleared` ORDER BY `Date Cleared` DESC ";

 

My question is, how do i run a similar query that will search both tables and combine the results.

 

I have tried adding the other database to the FROM statement, and have been tring to figure out if i can use a join statement but that hasn't yielded any favorable results.

 

[Wildbug]

You could use a UNION.

[grimmier]

Doh, Thanks for the reminder.

This seems to work.

 

$query = "SELECT COUNT( * ) AS `Count` , `Date Cleared` , `Tested By` 
FROM 
(
(Select * from `Inventory` WHERE `Tested By` = ".'"'.$tester.'"'." ) 
UNION 
(SELECT  *  FROM `Relief Inventory` WHERE `Tested By` = ".'"'.$tester.'"'." )
)
as `test`   
GROUP BY `Date Cleared` 
ORDER BY `Date Cleared` 
DESC "

 

Is there by chance anything simpler to speed the process up instead of doing the 2 seperate searches for everything?

[Wildbug]

That should be as fast as it gets.  MySQL is still scanning the same amount of data as if it were in the same table, more or less.  Also, you can use single quotes and variable interpolation within your double quotes instead of putting the $tester value in doubles:

 

....WHERE `Tested By` = '$tester' )....

 

Unless you have a better reason not to, it's a little cleaner.