CODEWITHSUNDEEP
mysqlsqldatabaseselect
Bogomip
Bogomip

Reputation: 437

GROUP_CONCAT, returning one row and null issues

I have a problem that seems similar to others posted before but the solutions to those don't really seem to work for me.

What I am trying to do is select rows from one database which relate to the particular user (found in another database) and then from a third database getting any attachments associated,so:

SO. I have this query:

SELECT msg_group.message, msg_group.starts, 
       msg_group.expires, 
       GROUP_CONCAT(msg_attach.file SEPARATOR ',')
FROM `membership`
INNER JOIN `msg_group` 
       ON membership.group_id = msg_group.group_id 
LEFT JOIN `msg_attach` 
       ON msg_group.id = msg_attach.ident AND msg_attach.type = "1"
WHERE membership.staff_id = "1"
AND msg_group.starts <= CURRENT_TIMESTAMP 
AND msg_group.expires >= CURRENT_TIMESTAMP 
ORDER BY msg_group.expires ASC LIMIT 0, 30

The problem with it is, there should be 5 rows being returned. One should, in the msg_attach.file columns have "file.jpg,file2.jpg". This is good, but then I should have 4 more rows under it with NULL in the msg_attach column.

To counter this I have tried using IFNULL(msg_attach.file, 'null') however all this returns is one row still but file.jpg, file2.jpg, null, null, null, null.

What I want is the row witch attachments to be file.jpg, file2.jpg and then 4 more rows will 'null' in that column.

I hope I have explained this well enough!#

Thanks for any assistance. Alex

edit: sample tables and data.

msg_group:

------------------------------------------------------------
| id | group_id | message               | starts | expires |
| 1  |   1      | PIES                  | date 1 | date 2  |
| 2  |   1      | CAKES                 | date 1 | date 2  |
| 3  |   1      | BEERS                 | date 1 | date 2  |
| 4  |   1      | POMMEGRANITES         | date 1 | date 2  |
------------------------------------------------------------

msg_attach:

---------------------------------
| id | type | ident| file       |
| 1  |  1   |  3   | corona.jpg |
| 2  |  1   |  3   | peroni.png |
---------------------------------

So I would want my query to return:

------------------------------------------------------------
| message       | starts | expires | file                  |
| PIES          | date1  | date2   | null                  |
| CAKES         | date1  | date2   | null                  |
| BEERS         | date1  | date2   | corona.jpg,peroni.png |
| POMMEGRANITES | date1  | date2   | null                  |
------------------------------------------------------------

What i get is:

------------------------------------------------------------
| message       | starts | expires | file                  |
| BEERS         | date1  | date2   | corona.jpg,peroni.png |
------------------------------------------------------------

or with the use of ISNULL:

------------------------------------------------------------------------------
| message       | starts | expires | file                                    |
| BEERS         | date1  | date2   | null, corona.jpg,peroni.png, null, null |
------------------------------------------------------------------------------

This is all omitting the membership and users stuff which I dont /think/ is relevant to the problem. Without GROUP_CONCAT (i.e. just msg_attach.file) I would get back 5 rows, just two for BEERS with the files listed individually.

Upvotes: 1

Views: 2240

Answers (1)

John Woo
John Woo

Reputation: 263723

The simplified query should looked like this,

SELECT  a.message, a.starts, a.expires,
        GROUP_CONCAT(b.file) fileList
FROM    msg_group a
        LEFT JOIN msg_attach b
            ON a.group_ID = b.type AND
                a.id = b.ident
GROUP   BY a.message, a.starts, a.expires

edited: by Bogomip as I dont know how to make a full comment to this:

Thanks, I have had to make a few changes to incorporate the users and stuff but this now works :)

SELECT  msg_group.message, msg_group.starts, msg_group.expires, GROUP_CONCAT(msg_attach.file) attachments
FROM    msg_group
INNER JOIN membership ON membership.group_id = msg_group.group_id 
LEFT JOIN msg_attach ON msg_group.id = msg_attach.ident AND msg_attach.type = "1"
WHERE membership.staff_id = "1"
AND msg_group.starts <= CURRENT_TIMESTAMP 
AND msg_group.expires >= CURRENT_TIMESTAMP 
GROUP   BY msg_group.expires, msg_group.starts, msg_group.message
ORDER BY msg_group.expires ASC LIMIT 0, 30

Upvotes: 1

Related Questions