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 4 of 4
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    418
    Thanks
    18
    Thanked 2 Times in 2 Posts

    combine multiple queries into one

    I have a mess of queries and i want to see if I can get most of them combined into one query to then echo out elements on a page with php.

    But that's a level of query-building a bit beyond my level...
    Everything depends on the numeric id variable ($id) Limited to just one result.

    I tried just a simple union between each, but got a warning of different number of columns.

    Here are the pieces I have. Maybe somebody can make simpler sense of this soup:

    query 1
    Code:
    SELECT descriptors.*, plantae.*, relationships.*, plant_pics.*
    FROM descriptors 
    JOIN plantae ON descriptors.plant_id = plantae.plant_name 
    JOIN relationships ON relationships.plant_id = plantae.plant_name
    LEFT JOIN plant_pics ON plant_pics.plant_id = plantae.plant_name AND plant_pics.part_id = 1 
    
    WHERE descriptors.plant_id = $id
    query 2:
    Code:
    SELECT DISTINCT continents.Name AS continent1,continent_regions.Name,country.Name AS country1, GROUP_CONCAT( states.Name SEPARATOR ', ') AS state1, plant_locations_link.plant_id
    FROM plant_locations_link
    JOIN continents ON plant_locations_link.continents = continents.ID
    JOIN continent_regions ON plant_locations_link.continent_regions = continent_regions.ID
    JOIN country ON plant_locations_link.country = country.ID
    JOIN states ON plant_locations_link.state = states.ID
    WHERE plant_locations_link.plant_id = $id
    query 3:
    Code:
    SELECT plant_term_id, plant_id, low_id, high_id, measurements1.* 
    AS lowest, measurements2.* AS highest,
    measurement_terms.plant_term AS tpart, plant_parts.part AS ppart
    FROM plant_measures
    JOIN measurement_terms ON plant_measures.plant_term_id =
    measurement_terms.id
    JOIN plant_parts ON measurement_terms.id = plant_parts.id
    LEFT JOIN measurements AS measurements1 ON plant_measures.low_id =
    measurements1.id
    LEFT JOIN measurements AS measurements2 ON plant_measures.high_id =
    measurements2.id
    WHERE plant_id = $id

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,869
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Maybe if you gave us the most basic clues as to what the output/result is supposed to be we could help you. But I don't know where to start from the queries you are showing us. They could be 100% wrong or they could be nearly right.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    418
    Thanks
    18
    Thanked 2 Times in 2 Posts
    sure. I wasn't sure what would be needed.

    All I want to do is collect a row of all the information from the different tables based on the plant id, then echo the information onto the page in various locations.

    Right now, each of those three queries is done individually on the page, and I'm hoping to understand how to combine them into one, cleaner query.

  • #4
    New Coder
    Join Date
    Oct 2013
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I don't think you really have a mess of queries here, perhaps your application code is getting a bit clunky because it has to merge the information from three queries?

    I don't think you can merge much here because I see lots of one-to-many relationships. one plant can be found on many contintents and even more countries. The query for locations will return several records per plant, while the query for the image wil return only one record per plant. If you merge this, you'll have to somehow force those records into a single list of data that has the plant data, the image data and the countrydata. Either something is going to be repeated, or fields will have to be merged in a comma delimiteted style.
    Neither are very easy to work with in your application.


  •  

    Posting Permissions

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