2

I have 5 tables

  • college
  • trade
  • district
  • contact
  • intake

Their relationship looks like this

table relation

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: my current result

Result that I want: required result

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.

  • I see a column called new between hostel and dname. Where is that coming from ??? – RolandoMySQLDBA Jun 26 '15 at 19:00
  • Its coming from college table. Ignore it, edited. – Arpan Das Jun 26 '15 at 19:05
  • GROUP BY trade – Rick James Jun 28 '15 at 7:12
0

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

  • Could you please explain about the last part - "A GROUP BY"? – Arpan Das Jun 27 '15 at 2:41
  • The section FROM ( up to ) A GROUP BY surrounds a subquery. The GROUP BY performs the aggregation of addresses for each combination of cname, ctype, tname, seats, hostel, dname. That combinations address so that the cname, ctype, tname, seats, hostel, dname are no longer duplicated. – RolandoMySQLDBA Jun 27 '15 at 3:21
0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.