MySql Join tables

Question

Hi Guys!

I have two tables like this.

Table 1 Fields: CId , Name Table 2 Fields: CId , food

I want to get no. of food against each CId with the query "select * from table1"

Answer 1

SELECT
   food
FROM
   Table2 t2
   JOIN Table1 t1 ON (t2.Cld = t1.Cld)

Answer 2

select a.name, b.food from table1 a, table1 b where a.cld = b.cld;

Answer 3

Something like that should work:

SELECT
  t1.* , count(t2.food) as foods
FROM
  t1 LEFT JOIN t2 on (t1.Cid = t2.Cid)
GROUP BY
  t2.Cid