mysql - Retrieve rows with more than 2 relations in another table -

i have 2 tables, table_a , table_b. table_a has following schema :

create table  table_a (   a_id int(11) unsigned not null auto_increment primary key,   varchar(255) not null unique,   b_id int(11) );  

and table_b :

create table table_b (   b_id int(11) unsigned not null auto_increment primary key,   b varchar(255) not null unique ); 

each element in table_a refers (in table_a.b_id) 1 element of table_b.

i want query output every element of table_b there 2 elements or more referencing in table_a (and each row, i'd display how many elements in table_a refer it)

thanks

try query:

select b.b_id, b.b, t.b_count table_b b inner join (     select a.b_id, count(*) b_count     table_a     group a.b_id     having count(*) > 1 ) t on b.b_id = t.b_id 

this avoids single group by query contain ambiguous columns (and therefore not run on sql server , other flavors).