Mysql – Problem with thesql group_concat, complex query, not getting required result

concatgroup-concatenationMySQL

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:

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.

Best Answer

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

Related Solutions

Mysql – GROUP_CONCAT with ORDER BY , but results are not ordered

How to Order GROUP_CONCAT() Values in MySQL

SELECT
*,
(SELECT
GROUP_CONCAT(url SEPARATOR '$$' ORDER BY priority)
FROM project_photos
WHERE project_id = projects.id
) AS images
FROM projects
WHERE catID = 2
LIMIT 0,5;

Mysql – Why group_concat and SQL_CALC_FOUND_ROWS do not work with each other

From the question itself, I see two problems

The return value of the query will be, at most, one row. SQL_CALC_FOUND_ROWS is uesless
You are looking for the number of rows that piled up in the GROUP_CONCAT result

You have to extrapolate the row count from the result of the group_concat. Here is how

If there is a return value from the GROUP_CONCAT, set @found_rows = 1 + the number of commas in the return value.
If no value is returned for the GROUP_CONCAT, set @found_rows = 0

BTW there is a heads you need to be aware of: GROUP_CONCAT's max length is 1024 by default. You should extend it to 10M.

SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(ca.id)
INTO @IDList
FROM notification AS ca 
LEFT JOIN alert_msg AS an ON an.id=ca.alert_msg_id
WHERE ca.mem_id='777 ' 
AND  ca.date!="" 
AND  ca.alert_msg_id 
NOT IN(26,27) AND ca.from_id != '777'

SET @found_rows = IF(
    LOCATE(',',IFNULL(@IDList,''))=0,0,
    LENGTH(@IDList) - LENGTH(REPLACE(@IDList,',','')) + 1
);

SELECT @found_rows,@IDList;

Give it a Try !!!

Best Answer

Related Solutions

Mysql – GROUP_CONCAT with ORDER BY , but results are not ordered

Mysql – Why group_concat and SQL_CALC_FOUND_ROWS do not work with each other

Related Question