mysql - Combining two tables in a database -

i have made 2 tables. first table holds metadata of file.

create table filemetadata ( id varchar(20) primary key , filename varchar(50), path varchar(200), size varchar(10), author varchar(50) ) ;  +-------+-------------+---------+------+---------+ | id    | filename    | path    | size | author  |  +-------+-------------+---------+------+---------+ | 1     | abc.txt     | c:\files| 2kb  | eric    |  +-------+-------------+---------+------+---------+ | 2     | xyz.docx    | c:\files| 5kb  | john    |  +-------+-------------+---------+------+---------+ | 3     | pqr.txt     |c:\files | 10kb | mike    |  +-------+-------------+---------+------+---------+ 

the second table contains "favourite" info particular file in above table.

create table filefav ( fid varchar(20) primary key , id varchar(20), favouritedby varchar(300), favouritedtime varchar(10), foreign key (id) references filemetadata(id)  ) ;  +--------+------+-----------------+----------------+ | fid    | id   | favouritedby    | favouritedtime |  +--------+------+-----------------+----------------+ | 1      | 1    | ross            | 22:30          |  +--------+------+-----------------+----------------+ | 2      | 1    | josh            | 12:56          |  +--------+------+-----------------+----------------+ | 3      | 2    | johny           | 03:03          |  +--------+------+-----------------+----------------+ | 4      | 2    | sean            | 03:45          |  +--------+------+-----------------+----------------+ 

here "id' foreign key. second table showing person has marked document his/her favourite. eg file abc.txt represented id = 1 has been marked favourite (see column favouritedby) ross , josh.

so wanna table/view shows info following -

+-------+-------------+---------+------+---------+---------------+ | id    | filename    | path    | size | author  | favouritedby  | +-------+-------------+---------+------+---------+---------------+ | 1     | abc.txt     | c:\files| 2kb  | eric    | ross, josh    | +-------+-------------+---------+------+---------+---------------+ | 2     | xyz.docx    | c:\files| 5kb  | john    | johny, sean   | +-------+-------------+---------+------+---------+---------------+ | 3     | pqr.txt     |c:\files | 10kb | mike    | null          | +-------+-------------+---------+------+---------+---------------+ 

how achieve this?

use join (from top of head, no checks done):

select filemetadata.id, filename, path, size, author, group_concat(favouritedby)  filemetadata  left join filefav on filemetadata.id=filefav.id group filemetadata.id