开发者

join with a group by?

i have a table called rc_language_type_table with:

id language
1 english
2 Xhosa
3 afrikaans

etc then i have a table rc_language_type_assoc_table with:

profile_id | language_type_id |
+------------+------------------+
| 3 | 1 | 
| 13 | 1 | 
| 15 | 1 | 
| 16 | 1 |

where i have profiles and each profile is connected to a language id in a 1 to many so then i did:

select *,count(*) from rc_language_type_assoc_table group by language_type_id;
+------------+------------------+----------+
| profile_id | language_type_id | count(*) |
+------------+------------------+----------+
| 3 | 1 | 96 | 开发者_如何学JAVA
| 3 | 2 | 19 | 
| 3 | 3 | 18 | 
| 64 | 4 | 51 | 
| 94 | 5 | 10 | 
| 37 | 6 | 26 | 
| 3 | 7 | 21 | 
| 3 | 8 | 4 | 
| 3 | 9 | 6 | 
| 88 | 10 | 4 | 
| 3 | 11 | 3 | 
+------------+------------------+----------+

what i want now is: instead having the language_type_id i want to display the actual language...how would i do this please???

i tried:

select *, count(*) 
from rc_language_type_assoc_table, rc_language_type_table 
group by language_type_id 
where rc_language_type_assoc_table.language_type_id = rc_language_type_table.id;

but i get a syntax error... please help??

thank you


GROUP BY should be "after" the WHERE statement and not before

select *, count(*) 
from rc_language_type_assoc_table, rc_language_type_table 
where rc_language_type_assoc_table.language_type_id = rc_language_type_table.id
group by language_type_id ;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜