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

    query several tables, based on link table

    Wow... I've really forgotten queries!

    I'm trying to figure out how to get the country, state, etc names from several tables, where they match entries in a link table.

    So, for example, the link table (plant_locations_link) has entries like this:

    Code:
    plant_id  |	state  | continents | continent_regions  | country
    ----------+------------+------------+--------------------+------------
    1234            49               3            12              129
    1234            47               3            12              129
    and I have a table for each of these rows, with the id/name of the country or state, etc.

    like so: Table continents:

    ID | Name
    -------------
    1 | asia
    2 | europe
    3 | North america


    I want to pull the Name field from the tables for continents, states, regions etc and display those, based on whether there's an entry in the link table for plant id 1234


    I've gotten to this point, but it just gives an empty result.

    Code:
    SELECT continents.Name AS continent1, continent_regions.Name AS continent_region1, country.Name AS country1,states.Name AS state1 FROM plant_locations_link 
    
    
    JOIN continents ON plant_locations_link.continent_regions = 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_id = 1234

  • #2
    Regular Coder
    Join Date
    Aug 2010
    Posts
    419
    Thanks
    18
    Thanked 2 Times in 2 Posts
    solved!

    ELECT DISTINCT continents.Name AS continent1,continent_regions.Name,country.Name AS country1, states.Name 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"


  •  

    Posting Permissions

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