22342

Left Join to most recent record

I have two tables

Games and Scores:

games has a primary key ID

Scores has game_id which references games.id and created on among other fields.

i am attempting to adapt the second example in the accepted answer of this question to fit my needs.

SELECT g.*, s.* FROM GAMES g LEFT JOIN SCORES s ON s.game_id = g.id JOIN (SELECT n.game_id, MAX(n.created_on) AS max_score_date FROM SCORES n GROUP BY n.game_id) y ON y.game_id = s.game_id AND y.max_score_date = s.created_on

it almost works, it gets the most recent entry in the score table for each game. however it only returns games which have a corresponding entry in the score table. and i need it to return all games in the table reguardless of if they have a entry in the score table. which from reading the previously cited question i assumed that the left join would accomplish.

Answer1:

SELECT g.*, s.* FROM GAMES g LEFT JOIN ( SCORES s INNER JOIN ( SELECT n.game_id, MAX(n.created_on) AS max_score_date FROM SCORES n GROUP BY n.game_id ) y ON y.game_id = s.game_id AND y.max_score_date = s.created_on ) ON s.game_id = g.id

Answer2:

You need an additional left outer join:

SELECT g.*, s.* FROM GAMES g LEFT JOIN SCORES s ON s.game_id = g.id LEFT OUTER JOIN (SELECT n.game_id, MAX(n.created_on) AS max_score_date FROM SCORES n GROUP BY n.game_id ) y ON y.game_id = s.game_id AND y.max_score_date = s.created_on

This version should do what you want:

select g.*, s.* from (select g.*, (select max(created_on) as maxcreatedon from scores s where s.game_id = g.game_id ) as maxcreatedon from games g ) g left outer join scores s on s.game_id = g.game_id and s.created_on = g.maxcreatedon

Recommend

  • Codeigniter 3 get a specific session data by id
  • Podio API Creating comments with additional attributes
  • Meteor handlebars template how to eval parameters
  • Blending languages - Python and Objective-C
  • “start” command in .bat batch file
  • Why won't astyanax (java) recognize my @Id annotated values in my scala case class parameter li
  • Can I have the market update an app that was installed from else where?
  • multiplayer game with bluetooth [iphone]
  • How to filter entities that are deleted using linq to entities
  • Change Divider Color Android DatePicker Dialog
  • How to resize image if the image is bigger than Textbox.But it not increase scale if smaller
  • Google OAuth2 for an web application hosted behind NAT (intranet server without public IP)
  • phpmailer - How to verify a sent email arrived at its destination
  • How to merge keras sequential models with same input?
  • Prevent page break in text block with iText, XMLWorker
  • Can I read an iPhone beacon with Windows.Devices.Bluetooth.Advertisement.BluetoothLEManufacturerData
  • Grails calculated field in SQL
  • Content-Length header not returned from Pylons response
  • Custom Tabgroup Appcelerator
  • Listbox within Listbox and scrolling trouble in Windows Phone 7 Silverlight
  • Disable Enter in editText android
  • Django: Count of Group Elements
  • Ajax Loaded meta Tags
  • Xamarin Forms - UWP Fonts
  • Linq Objects Group By & Sum
  • Read text file and split every line in MSBuild
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • How to check if every primary key value is being referenced as foreign key in another table
  • Arrow is showed instead of the material design version hamburger icon. Why doesn't syncState in
  • ORA-29908: missing primary invocation for ancillary operator
  • Jquery - Jquery Wysiwyg return html as a string
  • How to get next/previous record number?
  • Arrays break string types in Julia
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • WPF Applying a trigger on binding failure
  • python regex in pyparsing
  • How do you join a server to an Active Directory (domain)?
  • Java static initializers and reflection
  • embed rChart in Markdown
  • How does Linux kernel interrupt the application?