MySQL join question

Question

Hi,

Can anyone tell me if it is possible to combine the following two queries into one using a self-join and, if so, how to do it?

Query 1:

   SELECT pm.username AS user,
          uc.content_id AS id,
          value AS filename,
          name,
          moderation_status AS status,
          uc.parent_content_id
     FROM myweb.ugc_meta um
LEFT JOIN myweb.ugc_content uc ON uc.content_id = um.item_id
LEFT JOIN myweb.userbase_member pm ON uc.user_id = pm.id
    WHERE uc.content_type ='my.photo'
      AND uc.promoted = '1'
      AND moderation_status='passed'
    LIMIT 10

Query 2:

SELECT value
  FROM myweb.ugc_meta um
 WHERE um.item_id = '4780c650137a3409901286'
-- (item_id is the content_parent_id from query 1)

ugc_meta contains the album name for photos in ugc_content. ugc_content contains rows for albums AND photos. rows representing photos have a parent_content_id which is the content_id for the parent row. I don't have the option of changing the table or DB structure.

At the moment I am running a new query (query2) for every result row from query 1 which is sub optimal ;-)

Thanks!

Answer 1

if you want to self-join a table you have to assign a different aliase for each, then it should work without problems

   SELECT pm.username AS user,
          uc.content_id AS id,
          um.value AS filename, um2.value AS filename2
          um.name, um2.name AS name2
          moderation_status AS status,
          uc.parent_content_id
     FROM myweb.ugc_meta um
LEFT JOIN myweb.ugc_content uc
       ON uc.content_id = um.item_id
LEFT JOIN myweb.userbase_member pm
       ON uc.user_id = pm.id
LEFT JOIN myweb.ugc_meta um2
       ON id = um2.item_id
    WHERE uc.content_type ='my.photo'
      AND uc.promoted = '1'
      AND moderation_status='passed'
    LIMIT 10

hope this works!

Answer 2

You should be able to simply use a LEFT JOIN to the ugc_meta table using a different alias in order to achieve your parent table join like so:

SELECT pm.username AS user,
          uc.content_id AS id,
          value AS filename,
          name,
          moderation_status AS status,
          uc.parent_content_id,
       parent.`value`
     FROM myweb.ugc_meta um
LEFT JOIN myweb.ugc_content uc ON uc.content_id = um.item_id
LEFT JOIN myweb.ugc_meta parent ON uc.parent_content_id = parent.item_id
LEFT JOIN myweb.userbase_member pm ON uc.user_id = pm.id
    WHERE uc.content_type ='my.photo'
      AND uc.promoted = '1'
      AND moderation_status='passed'
    LIMIT 10

I used a LEFT JOIN because presumably there may not always be a parent_content_id in the child table, if there is always a parent in this case then you could use an INNER JOIN which would increase your performance gains more.

Answer 3

This should do it:

SELECT pm.username AS user,
      uc.content_id AS id,
      value AS filename, 
      name,
      moderation_status AS status,
      um2.value AS 
FROM myweb.ugc_meta um
LEFT JOIN myweb.ugc_content uc ON uc.content_id = um.item_id
LEFT JOIN myweb.userbase_member pm ON uc.user_id = pm.id
LEFT JOIN myweb.ugc_meta om2 ON um2.item_id = uc.parent_content_id
WHERE uc.content_type ='my.photo'
  AND uc.promoted = '1'
  AND moderation_status='passed'
LIMIT 10

You might have to put "um." in front of value, name and moderation_status if they're from ugc_meta as it will otherwise be ambiguous whether the value is to be taken from um og um2.