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

    Join Date
    Oct 2013
    Posts
    1
    Rep Power
    0

    SQL Join - Need help


    I will try to explain as best I can... I have a 'recent 20' query that I need to join with information from another table. The recent 20 has a UID and each row is unique and sorted by date. Another table has multiple entries for each UID (for image location). However, I only want to bring back 1 per UID.

    Query is this:
    [MYSQL]SELECT
    sof_slot_games.launch_date,
    sof_slot_games.game_name,
    sof_reviews.review_content,
    sof_slot_games.slot_game_id,
    sof_slot_game_details.no_of_reels,
    sof_slot_game_details.paylines,
    sof_reviews.reg_timestamp,
    sof_developers.developer_name,
    sof_slot_games.game_slug,
    sof_slot_game_images.game_image

    FROM
    sof_reviews
    Inner Join sof_slot_games ON sof_slot_games.slot_game_id = sof_reviews.slot_game_id
    Inner Join sof_slot_game_details ON sof_slot_games.slot_game_id = sof_slot_game_details.slot_game_id
    Inner Join sof_developers ON sof_slot_games.developer_id = sof_developers.developer_id
    left outer Join sof_slot_game_images ON sof_reviews.slot_game_id = sof_slot_game_images.slot_game_id
    WHERE
    sof_slot_game_images.image_type_id = '3'

    ORDER BY
    sof_slot_games.launch_date DESC
    limit 0,20[/MYSQL]

    The part that doesn't work is the 'left outer join' portion. I thought that was correct, but it brings back as many images as there are for each UID instead of only mapping to 1 of them.

    I hope that makes sense I boldened the text that I added to try to bring in 1 image per UID. Without that boldened text, the query brings back 20 most recent properly each with a unique slot_game_id (UID). Once I add the left outer join, I get multiple of the same slot_game_id (UID) with a different image (for as many images as there are based up on the image_type_id=3.

    thanks in advance!

    Covy
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by Covington
    However, I only want to bring back 1 per UID.
    give us some way to pick the one to return... except you cannot say "the first one"

    maybe the one with the earliest date? longest image name?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo