Jump to content

[SOLVED] Problems with CONCAT_WS()


xtopolis

Recommended Posts

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);

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.