Trouble with joining and the order of GROUP_CONCAT data

Question

I'm having a few tables from which i want to merge the data. I select basic information from the User table and want to join data from the tables User_Status, User_Sex_Info, User_Personal_Info and Media.

I'm encountering troubles with the data from the Media table, since this table can hold up many records of the same user, but i only want to display a maximum of six records (in order, In any case, one Type 1 image, followed by a maximum of five Type 2 images). The other tables only have one row of each user. I currently have the following table to select the data:


SELECT
    User.u_UserName, User.u_UserMail, User.u_UserRegistration, 
        Status.us_PaymentStatus, 
        Sex.us_Gender, Sex.us_Interest, 
            Personal.up_Name, Personal.up_Dob, Personal.up_City, Personal.up_Province, 
            UserMedia.m_Id, UserMedia.m_Type, SUBSTRING_INDEX(
                GROUP_CONCAT(
                    CONCAT(
                        UserMedia.m_Type, ':', UserMedia.m_File
                    )
                    ORDER BY UserMedia.m_Type = 1, UserMedia.m_Date DESC SEPARATOR '-'
                ),'-',6
            ) AS userFile   
FROM User AS User
    JOIN User_Status AS Status ON Status.User_u_UserId = User.u_UserId
    JOIN User_Sex_Info AS Sex ON Sex.User_u_UserId = User.u_UserId
        LEFT JOIN User_Personal_Info AS Personal ON Personal.User_u_UserId = User.u_UserId
        LEFT JOIN Media AS UserMedia ON UserMedia.User_u_UserId = User.u_UserId
WHERE User.u_UserId = :uId
GROUP BY UserMedia.m_Id

This one works fine on all the data, except for the media files. The output is 18-profile.jpg while i was expecting 18-profile.jpg-18-2.jpg-18-2.jpg-18-4.jpg-18-5.jpg-18-6.jpg

The Media table looks like this:

CREATE TABLE IF NOT EXISTS `Media` (
  `m_Id` int(11) NOT NULL AUTO_INCREMENT,
  `m_Type` enum('1','2','3') NOT NULL DEFAULT '3' COMMENT '1 = Profile picture\n2 = User picture\n3 = Content image',
  `m_File` varchar(255) NOT NULL,
  `m_Date` datetime NOT NULL,
  `User_u_UserId` int(11) NOT NULL,
  PRIMARY KEY (`m_Id`,`User_u_UserId`),
  UNIQUE KEY `m_id_UNIQUE` (`m_Id`),
  UNIQUE KEY `m_name_UNIQUE` (`m_File`),
  KEY `fk_Media_User1_idx` (`User_u_UserId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

INSERT INTO `Media` (`m_Id`, `m_Type`, `m_File`, `m_Date`, `User_u_UserId`) VALUES
(2, '1', '18-profile.jpg', '2013-07-30 00:00:00', 18),
(3, '2', '18-1.jpg', '2013-07-30 00:00:00', 18),
(4, '2', '18-2.jpg', '2013-07-30 00:00:00', 18),
(5, '2', '18-3.jpg', '2013-07-30 00:00:00', 18),
(6, '2', '18-4.jpg', '2013-07-30 00:00:00', 18),
(7, '2', '18-5.jpg', '2013-07-30 00:00:00', 18),
(8, '2', '18-6.jpg', '2013-07-30 00:00:00', 18);

Do i overlook something? I don't see why it isn't working.

EDIT I created a SQL Fiddle with the query and that shows my data in seven rows. I don't understand why it's not displaying any more rows when i run it locally

EDIT 2 Apparently the problem is in the GROUP_CONCAT since phpmyadmin shows the same result as the SQL Fiddle. I'm going to look at that

SOLVED IT! I grouped the data on the wrong parameter, i needed to group by User.u_UserId. Thanks for looking!

You're using `-` as the `GROUP_CONCAT` delimiter, but it's also a character in the filenames -- don't you think that's going to confuse things? — Barmar, Jul 30 '13 at 11:50
I've created a SQL Fiddle and thanks for the remark. I changed the separator to a | — Maurice, Jul 30 '13 at 12:14

score 0 · Accepted Answer · answered Jul 30 '13 at 12:20

0

Change

GROUP BY UserMedia.m_Id

to

GROUP BY User.u_userId

m_Id is unique for each Media row, so you weren't grouping anything together.

answered Jul 30 '13 at 12:20

Barmar

741,623
53
500
612

We found it at the same time :) Thanks! – Maurice Jul 30 '13 at 12:21

Trouble with joining and the order of GROUP_CONCAT data

1 Answers1

Linked