Can someone give me a pointer on this? I tried using INNERJOIN, etc to no avail.
Need to give myself a course in MySQL joins On Dec 19, 1:30 pm, "Jorge Vargas" <[email protected]> wrote: > On Fri, Dec 19, 2008 at 3:09 PM, Sean DiZazzo <[email protected]> wrote: > > > Thakns for the link. I'll give it a go. For some reason, the posts I > > found led me to believe that there was no inner join function. > > >> wow really my time machine should be broken. Because I haven't get to > >> that point in time yet. I'm still stuck as far away as TG2 release > >> party. > > > oh yeah...I meant TG2. I promise to upgrade when the TG2 book comes > > out!! > > did you heard that Mark, how is that coming along? :) > > > Would this be easier with sqalchemy?? > > oh yes SA has this very nice low layer api at the package > sqlalchemy.sql, which lets you work almost at the dbapi level but with > nice python-like constructs and it fairly > documentedhttp://www.sqlalchemy.org/docs/05/sqlexpression.html#selecting > > > On Dec 19, 12:52 pm, "Jorge Vargas" <[email protected]> wrote: > >> On Fri, Dec 19, 2008 at 12:39 PM, Sean DiZazzo <[email protected]> > >> wrote: > > >> > oops...record 3 filename should be foo.txt (matching the first > >> > record) The filenames for records 1& 3 match, so the query should > >> > only return records 1 & 2... 1 is more recent date than 3 > > >> ok so the table has duplicated records that really represent older > >> versions of the same file. and what you need is the newest of each > >> file for all files, right? > > >> > I found this sql, but it is untested ( un-understood too ): > > >> > I guess I should add the "order by date desc" as well... > > >> > """SELECT files.* FROM files INNER JOIN (SELECT MAX(id) AS id FROM > >> > files GROUP BY filename) ids ON files.id = ids.id""" > > >> > Last time I tried TurboGears 3 it was not ready for me... Plus, I'll > >> > need to dedicate some time to learn how SQAlchemy works, and I don't > >> > really have the time to dedicate at the moment. > > >> wow really my time machine should be broken. Because I haven't get to > >> that point in time yet. I'm still stuck as far away as TG2 release > >> party. > > >> You don't need tg2, SA is default since TG1.1, where the api change is > >> minimal (SO->SA, kid->genshi) > > >> > I found documentation for SQLobject on left join, max and group, but > >> > nothing for inner join. And since I can't even decipher it, I can't > >> > try to change the sql to work without having an sql inner join. > > >> how about > >> thishttp://www.sqlobject.org/SQLObject.html#left-join-and-other-joins > >> it explain how to use left join but it's analogous to the other joins > >> which are listed at the bottom of the section INNERJOIN is the name of > >> your function. > > >> > ~Sean > > >> > On Dec 19, 4:53 am, "Jorge Vargas" <[email protected]> wrote: > >> >> On Fri, Dec 19, 2008 at 12:12 AM, Sean DiZazzo <[email protected]> > >> >> wrote: > > >> >> > Hi group, > > >> >> > I'm a MySQL and Turbogears novice. Looking for some help in setting > >> >> > up a database query. I'm trying to query a table by a specific field > >> >> > (not unique), but only have the database return the most recent record > >> >> > of any duplicate record. > > >> >> > For example: > > >> >> > Files > >> >> > -------------------------------------- > >> >> > id | filename | date | > >> >> > -------------------------------------- > >> >> > 1 foo.txt 12_07_2008 > >> >> > 2 poo.txt 12_01_2008 > >> >> > 3 foo.text 12_06_2008 > > >> >> > I'd like to query the db so the resulting SearchResults object only > >> >> > contains records 1 & 2. > > >> >> sorry but your text doesn't match the example how are records 1 and 2 > >> >> duplicated? Do you have the raw SQL of what you want to accomplish? > > >> >> On a related note, unless you have a bug code base already build on > >> >> SO, I strongly suggest you start your migration towards SQLAlchemy, > >> >> for many reasons you can search the group for including that overthere > >> >> this is death simple. > > >> >> > I found some complex sql queries using inner joins, but couldn't > >> >> > figure out how to use them with sqlobject. I also tried to filter the > >> >> > results of a full query, but came to dead ends. > > >> >> > files = model.Files.select(???) > > >> >> > Any help would be appreciated! > > >> >> > ~Sean --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "TurboGears" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/turbogears?hl=en -~----------~----~----~----~------~----~------~--~---
