CODEWITHSUNDEEP
mysqljoincount
user3649739
user3649739

Reputation: 1869

Select Join two tables in mysql with a count/group on both

I'm just learning how to select from two tables with a where clause to match records using one field. So far I can do so using a group/count on on OR the other table e.g.

Select count(*),t1.*,t2.all 
from t1,t2 
where t1.Name=t2.name 
group by T1.date 
having count(*)>1

OR

Select count(*),t1.*,t2.all 
from t1,t2 
where t1.Name=t2.name 
group by T2.order 
# having count(*)>1

But I can't do it where both counts>1 (or any other # I choose).

I tried doing table.count(*) e.g.

Select t1.count(*),t1.*,t2.all 
from t1,t2 
where t1.Name=t2.name 
group by T1.date 
having count(*)>1

but mysql throws an error. Essentially I'd like to give various count requirements between table to get reports, just one count wont' work.

Here is a sql fiddle with count query from table1, count from table2 and looking for one that counts from both and one that gets ALL recods in Table2 where Table1 count > (notes in sqlfiddle)

http://sqlfiddle.com/#!9/c00d2/6

Upvotes: 1

Views: 299

Answers (1)

Alex
Alex

Reputation: 17289

You should read about JOIN and avoid using FROM t1, t2.

So your last query written properly I guess should be something like:

SELECT count(t1.id),
     t1.*,
     t2.all 
FROM t2
LEFT JOIN t1 
ON t1.Name=t2.name 
GROUP BY T1.date 
HAVING count(t1.id)>1

Upvotes: 0

Related Questions