mysql - join two table folder and image -

       album ------------------   aid  |  aname  |   ------------------   1   | album1   |    2   | album2   |    3   | album3   |    4   | album4   |  ------------------         image ---------------------------------------   iid  |  title  |  albumid |  imgurl ---------------------------------------   1   | img1     |   3      |  image3.jpg   2   | img2     |   1      |  image1.jpg   3   | img3     |   2      |  image4.jpg   4   | img4     |   0      |  image5.jpg --------------------------------------- 

i have 2 tables album , image. in image table albumid= 0 means image not belong folder. need imageurl folder names , count of images inside folder below table in mysql.

example:

----------------------------------------------------------------   id=aid+iid  |  name= aname +title | imgurl          | countimg ----------------------------------------------------------------   1           | album1              |       null      |  1   2           | album2              |       null      |  1   3           | album3              |       null      |  1   4           | album4              |       null      |  0   4           | img4                |    image5.jpg   |  0 ----------------------------------------------------------------- 

can have please. in advance.

try this:

select a.aid `id`, a.aname `name`, null imgurl, count(i.iid) countimg  album  left outer join image on a.aid = i.albumid   group a.aid  union  select a.iid `id`, i.title `name`, imgurl imgurl, 0 countimg  image  i.albumid = 0;