SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    IS CONCAT my solution

    Hi,

    I am getting the data from my table and for some columns, I need to blend their data.

    table
    | address_id| town | townland | city | county |

    I need the resultset to twons and cities and then city and county in the other (for drop down boxes) shoud, i use group concat for both groups or is there a more efficient way?

    bazz

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    would you kindly repeat the question? it doesn't make sense

    perhaps also show sample data and the results you expect
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    why does the message break between my brain and my fingers

    OK, my table is structured like this


    | town | townland | city | county |


    I need to get the data such that it is like this
    county
    -> city & town
    -> dining out ( for example)
    so, the first block of data is to be county, the second is to be a mixture of cities and towns in alphabetical order (cities first).

    I wonder if I should use group concat to put cities and counties into the one group of data in the returned resultset.
    bazz

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you want ORDER BY and simply format the results in your front-end language
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah you posted before I got this out..

    I was thinking along these lines....

    Code mysql:
    SELECT 
           addr.county
        group_concat(
          concat_ws(
              '/',
          (addr.city),
          (addr.town)
      ) ORDER BY addr.city, addr.town ASC
      ) as towns_and_cities
        ,  bt.business_type
        ,  bt.business_sub_type
        , bt.business_category
       FROM addresses addr
    inner
    join business_details bd
    on addr.business_id = bd.business_id
    inner 
    join business_types bt
    on bt.business_type_id = bd.business_type_id

    but I am not sure that I have the joins correct

    bazz

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    GROUP_CONCAT only works if the query has a GROUP BY clause, which i'm pretty sure you don't want here

    there is quite a difference between GROUP_CONCAT and CONCAT or CONCAT_WS

    why don't you rewrite your query with an ORDER BY clause and let me see what kind of results you get
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My previously-working query is below. however, I have changed my table so that instead of being like this


    city_or_county | town_or_townland | bleh

    it is now like this

    | town | townland | city | county | bleh


    So i need to find a way of putting the values from two columns into one returned value.

    previous working query
    Code mysql:
    SELECT 
      addr.city_or_county_name
    , addr.town_or_townland
    , bt.Business_Type
    , bt.Business_Sub_Type
    , bt.Business_Category
    , bd.business
    FROM address addr
    INNER 
    JOIN business_details bd
    on bd.business_id = addr.business_id
    INNER
    JOIN business_type bt
    on bt.business_type_id = bd.business_type_id
    I haven't overlooked your question / request rudy just, I am trying to explain more clearly with this post.

    bazz

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, it sounds like you could use CONCAT or CONCAT_WS

    why can't you just do the concatenation in your front-end?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I thought it would be more efficient to do all the heavy stuff with mysql. And since I don't know how to do it either in front end or mysql I thought I would try with mysql firstly.

    I'll look into concat and concat_ws again.

    Thanks Rudy.
    (I sent you an email earlier).

    bazz


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
  •