Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    420
    Thanks
    18
    Thanked 2 Times in 2 Posts

    query to figure out

    I'm so damn close, but not quite where I want this to be. I'm trying to get pull region / country / state locations, but my result is a step or two away from what I want.

    I have this query:
    Code:
    SELECT DISTINCT continents.Name AS continent1, continent_regions.Name, country.Name AS country1, GROUP_CONCAT( states.Name
    SEPARATOR ', ' ) AS state1, item_locations_link.item_id
    FROM item_locations_link
    JOIN continents ON item_locations_link.continents = continents.ID
    JOIN continent_regions ON item_locations_link.continent_regions = continent_regions.ID
    JOIN country ON item_locations_link.country = country.ID
    JOIN states ON item_locations_link.state = states.ID
    WHERE item_locations_link.item_id =83335
    This gives me :

    Code:
    continent1      |   Name 	  |   country1  |   state1 	               | plant_id
    ----------------+-----------------+-------------+------------------------------+---------------
    N. America      | N. America      |  U.S.   | Arizona, Texas, Sonora, Sinaloa  | 83335
    What I want is:

    Code:
    continent1      |   Name 	  |   country1 |   state1 	        | plant_id
    ----------------+-----------------+-------------+-----------------------+---------------
    N. America      | N. America      | U.S. 	| Arizona, Texas        | 83335
    N. America      | N. America      | Mexico 	| Sonora, Sinaloa       | 83335

    hopefully that makes sense?

  2. #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    4,006
    Thanks
    3
    Thanked 483 Times in 472 Posts
    Because your query doesn't contain a GROUP BY term and you are using GROUP_CONCAT, all the rows in the result set are being consolidated into one row.

    To do what you show, you need to add GROUP BY country.Name to form a group for each different country name.
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.

  3. Users who have thanked CFMaBiSmAd for this post:

    turpentyne (12-14-2013)

  4. #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    420
    Thanks
    18
    Thanked 2 Times in 2 Posts
    aha!

    That did it! Thanks so much.


 

Posting Permissions

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