need help with mysql query

[Roee]

Hi,

 

I've got complicated while trying to write the following mysq query. I hope you can help me to formulate it:

 

I have a table which is called tbl1 and contains m_id, active, name

Also I have another table which is called tbl2 and contains id, m_id

 

I would like to find the rows from tbl2 which their m_id = m_id of the rows in tbl1, and in tbl1 these rows are active='0'. Moreover, I want to get the results with the less appearance, something like order by mysq_num_rows.

 

How can I formulate this query?

 

THANK YOU VERY MUCH

ROEE

[Maq]

Didn't test this, but it should give you an idea.  Let me know if you have questions.

 

SELECT t2.id, t2.m_id, COUNT(t2.m_id) AS c 
FROM tbl1 t1 LEFT JOIN tbl2 t2 ON t1.m_id = t2.m_id 
WHERE t1.active=0 
GROUP BY t2.m_id 
ORDER BY c DESC;

[Roee]

Thanks alot!