[SOLVED] Problems with CONCAT_WS()

[xtopolis]

Been trying to produce several examples using CONCAT_WS() for a one to many relationship... no success.  I've done it in the past, but don't use it often enough, so I'm not sure why it's not doing what I expect.  My query:

SELECT j.jobID,j.jobDesc,CONCAT_WS(',',c.catName) FROM jobs j
JOIN jobs_categories jc ON(j.jobID = jc.jobID)
JOIN categories c ON(jc.catID = c.catID)
WHERE j.jobID = 1
GROUP BY j.jobID
ORDER BY j.jobID DESC

^-- this returns the correct job id, but only the first category listed from jobs_categories lookup table.  I want it to show Office, Technical instead of just Office.

 

My tables:

CREATE TABLE `jobs` (
  `jobID` int(11) NOT NULL auto_increment,
  `jobName` varchar(50) collate latin1_german2_ci NOT NULL,
  `jobDesc` varchar(500) collate latin1_german2_ci NOT NULL,
  PRIMARY KEY  (`jobID`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=3 ;

-- 
-- Dumping data for table `jobs`
-- 

INSERT INTO `jobs` VALUES (1, 'Programmer', 'Program for our company in C++');
INSERT INTO `jobs` VALUES (2, 'Secretary', 'Take phone calls, notes, schedule appointments.');


CREATE TABLE `categories` (
  `catID` int(11) NOT NULL auto_increment,
  `catName` varchar(50) collate latin1_german2_ci NOT NULL,
  PRIMARY KEY  (`catID`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=3 ;

-- 
-- Dumping data for table `categories`
-- 

INSERT INTO `categories` VALUES (1, 'Office');
INSERT INTO `categories` VALUES (2, 'Technical');


CREATE TABLE `jobs_categories` (
  `jobID` int(11) NOT NULL,
  `catID` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

-- 
-- Dumping data for table `jobs_categories`
-- 

INSERT INTO `jobs_categories` VALUES (1, 1);
INSERT INTO `jobs_categories` VALUES (1, 2);
INSERT INTO `jobs_categories` VALUES (2, 1);

[corbin]

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws

 

 

CONCAT doesn't work like that with GROUP BY.

[xtopolis]

SELECT j.jobID,j.jobDesc,CONCAT_WS(',',c.catName) FROM jobs j
JOIN jobs_categories jc ON(j.jobID = jc.jobID)
JOIN categories c ON(jc.catID = c.catID)
WHERE j.jobID = 1

 

Even without the group by, it returns 2 rows rather than 1 row concatenated.  Do you have an example query perhaps that shows what I want?

[corbin]

I don't know if you can do it with a MySQL query.

 

 

CONCAT combines columns, not rows (well technically columns from the same row), so you would need to force the data into columns in the same row.  You could do it with a join, but not with a variable number of category IDs.

[xtopolis]

Oh, duh.

Well then, I'm sorry to ask, but is there a way to concat the row values easily?... Or should I handle this in PHP?

[fenway]

There's a GROUP_CONCAT() function... but there are caveats.

[xtopolis]

GROUP_CONCAT was what I was looking for, thanks.

 

I read up and it says it's truncated to the max length (default 1024) and then the max_allowed_packet.  Are there other things I should look out for besides this?

 

Or is there a better way to accomplish what I want; concatenating row values?  [either by query, or table design]

[corbin]

There's a GROUP_CONCAT() function... but there are caveats.

 

 

 

Oh....  Hrmmm, never knew of that function before ;p.

[fenway]

Careful, though... it has a max_length.