Power By 专否社区

MySQL How do I complete this select concat?

Go To StackoverFlow.com

1

I have the following tables

http://sqlfiddle.com/#!2/647e9

How do I select concat to display as followed

Desired Results:

+---------+-----------+---------------+---------------------------------+
| Origin  | Stock     | Farm Title    |            Farm Values          |
+---------+-----------+---------------+---------------------------------+
| US      | P1        | Perdue        |                             333 |
| US      | P3        | Holstein      |                             825 | 
| CA      | Q4        | FarmOne       |         455,536,617,698,779,860 |
| CA      | Q4        | Beef Farm     |         540,550,560,570,580,590 |
| CA      | Q4        | CattleOne     |   1080,1100,1120,1140,1160,1180 |
| MX      | B3        | Cow Mill      |                              11 |
| MX      | B3        | Dotterers     |                              98 | 
| MX      | B3        | AgriZone      |                             202 |
+---------+-----------+---------------+---------------------------------+

I tried

Select
ORIGIN_NAME
STOCK_TITLE
FARM_TITLE
concat(FARM_VALUES, ",")

From Farm f
JOIN STOCK s on S.S_ID=f.S_ID
JOIN ORIGIN o on o.ORI_ID=s.ORI_ID
2012-04-04 23:49
by stackoverflow
What two fields are you concating and what do they look like - Ryan 2012-04-04 23:52
@RPM the tables are found here http://sqlfiddle.com/#!2/647e9 I'm only trying to concat (comma delimited) every value for that particular farm titl - stackoverflow 2012-04-04 23:55
You need to edit your post to provide the table info here. Posting it on an external site is not generally acceptable. First, it makes people have to leave this site in order to even read your question. Second, it makes your question (and any answers) meaningless if that external site is not available or goes away in the future. And finally, it's not searchable by future users. Please edit your post to provide the info here (and review the FAQ concerning making your questions and answers self-sufficient. Thanks. : - Ken White 2012-04-04 23:57
Perdue has STOCKID 3, but P1 has STOCKID 1 - so why does your desired results match them together? In fact, your desired results don't seem to match up with your tables at all - mathematical.coffee 2012-04-04 23:57
@KenWhite I would assume that showing a constructed database and allowing others to fiddle with it would be more constructive than a static picture of my table - stackoverflow 2012-04-05 00:03
@mathematical.coffee sorry I didnt make that connection until you pointed it out. Thank you thoug - stackoverflow 2012-04-05 00:04
As I said, if it's not available here it's meaningless in the future. You don't need a constructed database to ask your question; a small description of the schema and sample data would suffice, and would make your question self-sustaining. I'm giving you the opportunity to improve it (and bring it into compliance with the site guidelines) instead of downvoting or voting to close. : - Ken White 2012-04-05 00:05
@KenWhite thanks for the advice. I'll be sure follow more close to the site guidelines next tim - stackoverflow 2012-04-05 00:10

4

The data is a bit different in the fiddle, so the results are not exactly right, but this seems to be what you're going for:

select origin_name, stock_title, farm_title, group_concat(FARM_COMPONENETS)  from ORIGIN o
inner join STOCK s on o.ORIGIN_ID = s.ORIGIN_ID
inner join FARM f on s.stock_id = f.stock_id 
inner join gate g on f.farm_id = g.farm_id
group by origin_name, stock_title, farm_title
2012-04-04 23:58
by Nathan Labenz
Relevant documentation: <code>GROUP_CONCAT</code>. It concatenates all values with a comma separator within each level of your GROUP BY variable(s) - mathematical.coffee 2012-04-05 00:00
Ads