CODEWITHSUNDEEP
mysqlselectcount
Heskemo Kam
Heskemo Kam

Reputation: 1

mysql select row and count each connected distinct row

table one

product  c  
==========================================
sample_1    Line
sample_1    LLk
sample_1    LLk
sample_1    LLk
sample_1    LLk
sample_2    LLk
sample_3    LLk
sample_2    LLk
sample_3    LLk
sample_2    Line

Plus this table two

a    h        r 
==========================================
LLk  sample_1  gw
LLk  sample_2  okp
LLk  sample_3  ijof
Line xe     rr3
LLk  sample_4  gr2
Line xx2       o3

Finally will be like this..

based on searching from table 2 where a=LLk

this is the table 3 which generated from table 1 and table 2

product  counts
============================
sample_1    4
sample_2    1
sample_3    2
sample_4    0

the detail link for this question http://pastebin.com/ds0iS4GU

Upvotes: 0

Views: 416

Answers (2)

praveen
praveen

Reputation: 78

This will give you the answer you are looking for

select h, count(t1.product) from t2 LEFT JOIN t1 ON t1.c=t2.a and t1.product=t2.h where t2.a='LLk' group by t2.h

Upvotes: 1

P4ul
P4ul

Reputation: 770

are you looking for something like this?

select product, count(a) 
from table_two 
inner join table_one on product=h and c=a
where a = 'LLk'
group by product

Upvotes: 0

Related Questions