SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    london
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with subquery / group concat

    Hi,

    I've attached some data from which I want to extract individual itinerary ids and show the ships which go to those destinations. The problem is that the destination ids are stored as comma separated values in a single field and there are rows which duplicate the same associations between the ship and destination ids. I can do:

    SELECT ship_id, GROUP_CONCAT(DISTINCT destination_ids) FROM itineries GROUP BY ship_id;

    and get a list of destination ids for each ship but what I actually want is a list of ships for each destination Id.

    Can somebody give me some pointers on how to achieve this? I think it might be possible with a subquery but am not sure how to go about this. MYSQL version is 4.1.20.

    redesigning the database is not an option. i need to generate the results from the table as it is if possible.

    thanks!

    lukemack.
    Attached Files Attached Files

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by lukemack View Post
    redesigning the database is not an option
    my sincere condolences, and i am honestly not trying to be sarcastic

    the sql is hideous and complex
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •