How do I write a My(SQL) query that counts from multiple tables based on specific WHERE clause criteria

Question

I have 5 tables: a, b, c, d and e.

Each table is joined by an INNER JOIN on the id field.

My query is working perfectly fine as it is but I need to enhance it to count the result so I can echo it to the screen. I have not been able to get the count working.

There are very specific fields I am querying:

state_nm
status
loc_type

These are all parameters I enter manually into the query like so:

$_POST["state_nm"] = 'AZ'; ... // and for all other below values..

SELECT *
FROM main_table AS a
INNER JOIN table_1 AS b ON a.id = b.id
INNER JOIN table_2 AS c ON b.id = c.id
INNER JOIN blm table_3 AS d ON c.id = d.id
INNER JOIN table_4 AS e ON d.id = e.id
WHERE a.trq != ''
   AND b.state_nm = '".$_POST["state_nm"]."'
   AND b.loc_type LIKE \ "%".$_ POST ["loc_type"]."%\"
   AND b.STATUS = '".$_POST["status"]."'
GROUP BY b.NAME
ORDER BY c.county ASC;

For displaying the count, you could look at [mysql_num_rows](http://php.net/manual/en/function.mysql-num-rows.php) — Adam Wenger, Jul 14 '13 at 02:46
Please read [Why does MySQL add a feature that conflicts with SQL standards?](http://stackoverflow.com/questions/7594865/why-does-mysql-add-a-feature-that-conflicts-with-sql-standards) — Kermit, Jul 16 '13 at 00:25

score 0 · Answer 1 · answered Jul 13 '13 at 15:46

not sure I get exactly what is your goal here. anyway, using "select *" and group by in the same query is not recommended and in some databases will raise an error what I would do is something like that:

select a.name, count(*) from (
SELECT * FROM main_table as a 

INNER JOIN table_1 as b 

ON a.id=b.id

INNER JOIN table_2 as c

ON b.id=c.id 

INNER JOIN blm table_3 as d 

ON c.id=d.id 

INNER JOIN table_4 as e

ON d.id=e.id  

WHERE a.trq != '' 

AND b.state_nm = '".$_POST["state_nm"]."'  

AND b.loc_type LIKE \"%".$_POST["loc_type"]."%\"  

AND  b.status = '".$_POST["status"]."' 
)a
group by a.name

the basic idea is to add an outer query and use group by on it...

hopefully this solves your problem.

spencer7593 · Answer 2 · 2013-07-14T02:20:09.033

-1

In place of

SELECT *

in your query, you could replace that with

SELECT COUNT(*)

That query should return the number of rows that would be in the resultset for the query using SELECT *. Pretty easy to test, and compare the results.

I think that answers the question you asked. If not, I didn't understand your question.

I didn't notice the GROUP BY in your query.

If you want to get a count of rows returned by that query, wrap it in outer query.

SELECT COUNT(1) FROM (
  /* your query here */
 ) c

That will give you a count of rows returned by your query.

edited Jul 14 '13 at 02:20

answered Jul 13 '13 at 02:33

spencer7593

106,611
15
112
140

I'll give this a shot and let you know. Thanks. – OldWest Jul 13 '13 at 04:11
My answer received downvotes. If this doesn't answer the question the OP asked, and isn't useful to the OP, then I'll be removing this answer. I apologize for whatever is wrong with this answer that it received downvotes. – spencer7593 Jul 13 '13 at 04:35
Downvoting without giving a reason does not help improve the answer, no one know what you disagreed with. – Adam Wenger Jul 14 '13 at 02:36

How do I write a My(SQL) query that counts from multiple tables based on specific WHERE clause criteria

2 Answers2