#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    31
    Rep Power
    2

    Join or Union for SELECTING same table twice


    I have two tables in a prepackaged system, so I can't refactor them or a lot of things break.

    Table 1: Metadata
    id
    entity_id (for user id)
    name_id
    value_id

    Table 2: Metastrings
    id
    string

    So, Table one holds the indexes for the meta strings. Name_id corresponds to one record in Table 2 with the metadata name (i.e. first_name) and value_id corresponds to another record in Table 2 with that value (i.e. Michael)

    What I need is a single query to get the name and value into a single result for each user by entity_id AS userid

    I have tried to INNER JOIN the metastrings table twice
    Code:
    SELECT
    elgg_metadata.entity_guid AS userid, 
    elgg_metadata.name_id,
    elgg_metadata.value_id,
    elgg_metastrings.string
    
    FROM elgg_metadata
    
    INNER JOIN elgg_metastrings
    ON elgg_metadata.name_id=elgg_metastrings.id
    
    INNER JOIN elgg_metastrings
    ON elgg_metadata.value_id=elgg_metastrings.id
    
    WHERE elgg_metadata.entity_guid=35
    to no avail. Either INNER JOIN works until the other is added.

    I have also tried UNION and UNION ALL similarly

    Code:
    SELECT
    elgg_metadata.entity_guid AS userid, 
    elgg_metadata.name_id,
    elgg_metastrings.string
    
    FROM elgg_metadata
    
    INNER JOIN elgg_metastrings
    ON elgg_metadata.name_id=elgg_metastrings.id
    
    WHERE elgg_metadata.entity_guid=35
    
    UNION ALL 
    
    SELECT
    elgg_metadata.entity_guid AS userid, 
    elgg_metadata.value_id,
    elgg_metastrings.string
    
    FROM elgg_metadata
    
    INNER JOIN elgg_metastrings
    ON elgg_metadata.value_id=elgg_metastrings.id
    
    WHERE elgg_metadata.entity_guid=35
    Which I think returns everything as separate records, and calls all the values name_id

    What I need is a result like

    user_id -> 35
    name -> first_name
    value -> Michael

    I am sure there is an easier way to do this. Do I need to run multiple queries and story things in PHP variables to be combined later?

    Thank you for the help.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Code:
    SELECT elgg_metadata.entity_guid AS userid
         , elgg_metadata.name_id
         , elgg_metadata.value_id
         , meta_1.string AS name_string
         , meta_2.string AS value_string
      FROM elgg_metadata
    INNER 
      JOIN elgg_metastrings AS meta_1
        ON meta_1.id = elgg_metadata.name_id
    INNER 
      JOIN elgg_metastrings AS meta_2
        ON meta_2.id = elgg_metadata.value_id
     WHERE elgg_metadata.entity_guid = 35
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    31
    Rep Power
    2
    Works perfectly. Thanks. Looking at your book now!

IMN logo majestic logo threadwatch logo seochat tools logo