SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: join stuff

  1. #1
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    237
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    join stuff

    PHP Code:
    SELECT DISTINCT(videos.GTitle), videos.systemgames.yeargames.gamegames.boximage FROM games
    INNER
    JOIN
     videos
     ON videos
    .GTitle games.game AND videos.system games.system
    WHERE games
    .year>=2005 ORDER BY videos.ID DESC LIMIT 4 
    I want to show 4 games which have the newest videos. This right now is not working as it shows just the 4 newest added games.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    could you show the table layouts and a few rows of data please?

    best would be mysqldump output -- CREATE TABLE statements, and a few INSERTs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    237
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Will this do?


  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, without sample data i don't get it

    perhaps you could explain how the tables are related, whether it's one-to-many or many-to-one, and how you would determine which ones are the "newest videos"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    237
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am trying to select 4 videos where GTitle is DISTINCT - ordered by ID to get the newest 4 videos which are all from different games.

    But I want to display the 4 games, not the videos. Right now it is not displaying the correct games.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    well, i still don't understand the relationship so i can summarize the approach like this --

    get the max(videoID) for each game

    sort by this maxID DESC and use LIMIT 4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Apr 2005
    Posts
    237
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool. MAX did it. It wasn't for videoid as that is something different, but I got it working thanks to you. Thanks alot! Sorry I couldn't have been more informative.

    SELECT MAX(videos.ID) AS vidid, videos.GTitle, videos.system, games.year, games.game, games.boximage FROM games
    INNER
    JOIN
    videos
    ON games.game = videos.GTitle AND games.system = videos.system
    WHERE games.year>=2005 GROUP BY videos.GTitle ORDER BY vidid DESC LIMIT 4


Bookmarks

Posting Permissions

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