Problem with mysql group_concat, complex query, not getting required result I have 5 tables


college
trade
district
contact
intake


Their relationship looks like this



I'm having problem with the contact table. In this table, cid and location are the primary key. It can have only two location campus and office for each cid i.e. college id. Table intake connects college and trade table.
With this query below, I'm getting results twice for each address.
This is my query: 

SELECT cname, ctype, tname, seats, hostel, new, dname, address, ph_no, email, website
FROM college, trade, district, intake, contact
WHERE intake.cid = college.cid and intake.tid = trade.tid and college.did = district.did and contact.cid = college.cid
ORDER BY cname ASC;

My current result:


Result that I want:


I know this can be achieved by grop_concat and concat function but I don't know how to use these functions in this situation. Somebody please help me to build the query to achieve this result.

Thank you.
It will be something like this:

SELECT cname, ctype, tname, seats, hostel, dname,
GROUP_CONCAT(addr SEPARATOR '\n') addresses
FROM
(
    SELECT cname, ctype, tname, seats, hostel, dname,
    CONCAT('Address: ',address,',Phone:',ph_no,',Email:',email,',Website:',website) addr
    FROM college, trade, district, intake, contact
    WHERE intake.cid = college.cid
    and intake.tid = trade.tid
    and college.did = district.did
    and contact.cid = college.cid
    AND cname LIKE '%{$cname}%'
    AND tname LIKE '%{$tname}%' {$ctype}
    AND dname LIKE '%{$dname}%' {$hostel}
    ORDER BY cname ASC
) A GROUP BY cname, ctype, tname, seats, hostel, dname;


Since group_concat_max_len has a default of 1024, you may need do two more things to accommodate GROUP_CONCAT results longer that 1024

First, login to MySQL and run

mysql&gt; SET GLOBAL group_concat_max_len = 1024 * 1024;


Then, add this to my.cnf under the [mysqld] group header

[mysqld]
group_concat_max_len=1M


GIVE IT A TRY !!!