8

I have a a table with the following data:

reservno || icode || location
00004    || 00021 || Bohol - Cebu
00004    || 00022 || Cebu - Manila
00004    || 00014 || Manila - Bohol

I use this query to retrieve the concatenated value of location.

SELECT GROUP_CONCAT(location) from location_list where reservno='00004';

The query result looks like this:

GROUP_CONCAT(location)
Bohol - Cebu,Cebu - Manila,Manila - Bohol

But what I want to do is for the query to look like this: Bohol - Cebu - Manila - Bohol. I would like to merge the result like that. How can I achieve this? I'm not that familiar with MySQL string functions so I need some ideas on how to make this work. Any help will be appreciated. Thanks a lot!

Improve this question
1
  • 2
    How do you determine the order in which records should be joined? Commented Aug 29, 2012 at 5:55

3 Answers 3

Reset to default
6

You need to use SEPARATOR in GROUP_CONCAT function:

SELECT  GROUP_CONCAT(IF((@var_ctr := @var_ctr + 1) = @cnt, 
                        location, 
                        SUBSTRING_INDEX(location,' - ', 1)
                       ) 
                       ORDER BY loc_id ASC
                       SEPARATOR ' - ') AS locations

FROM location_list, 
     (SELECT @cnt := COUNT(1), @var_ctr := 0 
      FROM location_list 
      WHERE reservno='00004'
     ) dummy
WHERE reservno='00004';

Example: SQLFIDDLE

It's not a good practice to store multiple values in same column, Better way could be:

reservno || icode || location_from || location_to
00004    || 00021 || Bohol         || Cebu
00004    || 00022 || Cebu          || Manila
00004    || 00014 || Manila        || Bohol
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

@xjshiya It is not guaranteed this will come up in the order you necessarily want though. You should make sure you specify an order by clause.
Yeah. That's what I'm trying to figure out next. Thank you all for your help! :)))
That's a great idea and I had that on mind before. But I have a another table (itinerary) which stores the, icode (itinerary code), location (e.g. Tokyo - London) and the btime (block time: hours to spend travelling an itinerary, e.g. 5:00:00 (5 hours). I can implement it like you suggested but I think it'll be more complicated. Plus, the issue of redundancy. But I think I'll reconsider through brainstorming. Thanks anyway! :)))
@Omesh, sorry if this is a bit late, how can I apply the order by clause in this code? Tried it a lot of times but I can't get it to work as I want. I added loc_id as the primary key of the table and it should be ordered by that in ascending order. Thank you so much!
It doesn't seem to have the problem I mentioned even if I edit it at sqlfiddle. It involves php & mysql coding after all so I guess I'll just post a new question. :)
|
3

Here's one way. It separates out the two locations from your single location column, distincts it, then puts it back together SQL Fiddle:

SELECT GROUP_CONCAT(DISTINCT loc SEPARATOR ' - ')
FROM 
(
    SELECT SUBSTRING_INDEX(location, ' - ', 1) AS loc
    FROM location_list 
    WHERE reservno='00004'
    UNION ALL
    SELECT SUBSTRING_INDEX(location, ' - ', -1) AS loc
    FROM location_list 
    WHERE reservno='00004'
) separatedLocs

You might be looking for something more intelligent though? I sense "location" is more like a "from-to" kind of thing? I also question the decision of storing more than one value in your location column like this. You would be much better off storing a location1 and location2 as separate columns.

Improve this answer

6 Comments

It resturns: Bohol - Cebu - Manila. Is there any way to still add the Bohol at the last part? Like this: Bohol - Cebu - Manila - Bohol? Thanks for the answer!
@xjshiya How do you determine the order they should be joined in then?
I mean what should the output be if the records returned are: "Manila - Bohol";"Cebu - Manila";"Bohol - Cebu"
That input isn't allowed. The inputs are places, e.g London - Tokyo. It's like the users are choosing a place to go. So if the user chose London - Tokyo, the next input he/she got to choose from are Tokyo - Canada, Tokyo - Singapore etc. It's like a cycle and the last input should be 'anywhere - London' since the user started off at London. Sorry for the wordy explanation. :)))
@xjshiya Ok, but how do you get that order then? Should you order by icode??
|
0

As the currently accepted answer seems to cause problems later on, here is another alternative. It is heavily based on the solution by lc. but takes care of ordering.

First off: SQL tables have no well-defined row order. When you query for all rows of a table, those rows will be returned in a certain order, but that order is undefined, may or may not be related to the order in which rows were added, and may change unexpectedly. So when you want to process your rows in a given order, you should add a column to contain some kind of sequence number or smilar. That's the reason why lc. kept bugging you about the order of locations.

If you have such a column, called seq, then you can use the following query:

SELECT GROUP_CONCAT(loc SEPARATOR ' - ')
FROM (
 (SELECT 1 half, seq, SUBSTRING_INDEX(location, ' - ', 1) loc
  FROM location_list
  WHERE reservno='00004'
  ORDER BY seq
  LIMIT 1)
 UNION ALL
 (SELECT 2 half, seq, SUBSTRING_INDEX(location, ' - ', -1) loc
  FROM location_list
  WHERE reservno='00004')
 ORDER BY half, seq
) locs

The union will produce a list of the various locations, which is combined into a single string using the outermost select. The first part of the union yields the first half of the first part of the route, whereas the second part of the union will give you the second half of all parts. The order of the union result is undefined so far, so we need an overall ordering rule. We also need an ordering rule for the first half, so that we really choose the first part of the route with the limit clause.

Here is an sqlfiddle based on the one Omesh set up. For lack of a seq column, it uses the icode column to order things. Therefore the result differs from the one you expected, and produce Manila - Bohol - Cebu - Manila instead.

If you add a seq column, then you'll have to change the database schema, so you might as well change it in such a way that the two endpoints of each part are turned into two distinct columns. Combining columns using CONCAT is simple, but splitting them increases complexity of the queries both for the developer and the database engine.

If you cannot add a sequence column, because you don't have control over the database schema, then you're in trouble. Your comment here indicates that you're always looking for a cycle, but finding such a cycle from unordered data is best done using a map, which isn't readily available at the SQL level. You could achieve this through a stored procedure if you must, executing one select for each part of the jurney, but I'd rather tackle this at the application level if I were you.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.