What happened to the Messenger Plus! forums on msghelp.net?
Shoutbox » MsgHelp Archive » Skype & Technology » Tech Talk » Need some help with some MySQL trickery

Need some help with some MySQL trickery
Author: Message:
MeEtc
Patchou's look-alike
*****

Avatar
In the Shadow Gallery once again

Posts: 2200
Reputation: 60
38 / Male / Flag
Joined: Nov 2004
Status: Away
O.P. Need some help with some MySQL trickery
After a lot of tinkering around with this query, I'm still having some difficulty getting MySQL to return the data that I want it to.

Some background info:
database structure of the parts in question
I have the following query:
code:
SELECT `notes`, `itemid` FROM yass_log WHERE itemid IN
   (SELECT `itemid` FROM yass_image_items WHERE `uid` = 1)
AND `notes` != ''


Results in the following data:
code:
notes          itemid
its a MS font    1
i made this! :D  2
its good (Y)     3
its too ugly     4
nice             5
foobar           52
barfoo           52


What I am trying to to accomplish is to return only the last note written for a particular itemid. I have been using itemid 52 as my test. Adding GROUP BY itemid removes the multiple notes for any one itemid, but will only show the first note (in the sample data, 'foobar'). What I want to happen is to have the last note be returned in the data set ('barfoo')
I have tried adding many combinations in an ORDER BY clause, but each time 'foobar' keeps being returned.

Can anyone help out?

This post was edited on 09-08-2008 at 01:28 AM by MeEtc.
[Image: signature/]     [Image: sharing.png]
I cannot hear you. There is a banana in my ear.
09-08-2008 01:28 AM
Profile PM Web Find Quote Report
MeEtc
Patchou's look-alike
*****

Avatar
In the Shadow Gallery once again

Posts: 2200
Reputation: 60
38 / Male / Flag
Joined: Nov 2004
Status: Away
O.P. RE: Need some help with some MySQL trickery
Its been answered in IRC, thanks to Adeptus
code:

SELECT a.`notes`, a.`itemid`
FROM yass_log a
JOIN
   (SELECT MAX(`logid`) AS logid
   FROM yass_log
   GROUP BY `itemid`) b
ON a.`logid` = b.`logid`
JOIN yass_image_items c
ON a.`itemid` = c.`itemid`
WHERE c.`uid` = 1
AND a.`notes` <> ''


This post was edited on 09-08-2008 at 02:29 AM by MeEtc.
[Image: signature/]     [Image: sharing.png]
I cannot hear you. There is a banana in my ear.
09-08-2008 02:21 AM
Profile PM Web Find Quote Report
Dempsey
Scripting Contest Winner
*****

Avatar
http://AdamDempsey.net

Posts: 2395
Reputation: 53
38 / Male / Flag
Joined: Jul 2003
RE: Need some help with some MySQL trickery
Ah well done Adeptus!  He helped me out with some SQL stuff before (Y)
SoundPacks   -   Scripts   -   Skins

that's not a bug, thats an unexpected feature
09-08-2008 07:40 AM
Profile E-Mail PM Web Find Quote Report
« Next Oldest Return to Top Next Newest »


Threaded Mode | Linear Mode
View a Printable Version
Send this Thread to a Friend
Subscribe | Add to Favorites
Rate This Thread:

Forum Jump:

Forum Rules:
You cannot post new threads
You cannot post replies
You cannot post attachments
You can edit your posts
HTML is Off
myCode is On
Smilies are On
[img] Code is On