Problem with mysql group_concat, complex query, not getting required result

Question

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.

Answer 1

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> 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 !!!

Answer 2

This should work for you:

SELECT 
    cname, 
    ctype, 
    tname, 
    seats, 
    hostel, 
    dname, 
    GROUP_CONCAT(address SEPARATOR '\n') AS 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
GROUP BY cid
ORDER BY cname ASC;

I added GROUP_CONCAT(address SEPARATOR '\n'), that makes a line skip between addreses and GROUP BY cid grouping it by cid.

Answer 3

Solved, the query is:

    $sql = "SELECT cname, ctype, tname, seats, dname, hostel,
            GROUP_CONCAT(addr SEPARATOR '<hr />') addresses
            FROM
            (
                SELECT cname, ctype, tname, seats, dname, hostel,
                CONCAT('<b>Address:</b> ', IFNULL(address, 'N/A'),
                        ',<br /><b>Phone:</b> ', IFNULL(ph_no, 'N/A'),
                        ',<br /><b>Email:</b> ', IFNULL(email, 'N/A'),
                        ',<br /><b>Website:</b> ', IFNULL(website, 'N/A')
                    ) 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 '%{$name}%' 
                    AND tname LIKE '%{$trade}%' 
                    {$type} 
                    AND dname LIKE '%{$district}%' 
                    {$hostel}
                ORDER BY cname ASC
            ) A GROUP BY cname, ctype, tname, seats, dname, hostel";

Not adding IFNULL was creating problem. Originally answered by RolandoMySQLDBA. Thanks.