List:General Discussion« Previous MessageNext Message »
From:Richard Date:December 28 2007 10:16pm
Subject:help with a query...
View as plain text  
Hello, I'm trying to get what is for me quite a complicated query to 
work, if it's possible to do so anyway ...
Here is my old query :
 SELECT a.message,a.date,b.surname,b.name,b.title FROM messages a JOIN 
info b ON a.from=b.code WHERE a.id='28'
It worked fine untill I needed to have more than one 'code' in the info 
table ...
To get data from the info table I do this :
 SELECT * FROM info WHERE code LIKE $code ORDER BY num DESC LIMIT 1
And the reason for this is I allow members to change their data, but I 
need to keep their old data.
Because there are for example 3 lines with the same code in the messages 
table the messages are repeated 3 times.
My question is, is there a way to limit the answer to once ?
In otherwords this is what I want to do :
 SELECT a.message,a.date,b.surname,b.name,b.title FROM messages a JOIN 
info b ON a.from=(b.code ORDER BY b.num DESC LIMIT 1) WHERE a.id='28'
Do you understand what I mean? I know it's not very clear ... :)

Well here goes again just incase :
I've got two tables. one called messages and the other called info.
The messages table contains :

 id => autoincrement key
 from => members code who sent message
 date => date when message was sent

For each message I need to get the firstname, surname and title which 
are stored in the info table.
The info table contains :

 num => autoincrement key
 code => member code
 name => firstname
 surname = > surname
 title => Sir, Miss or Mrs

However each time a member changes his/her info it creates a new line 
instead of changing the old one. This is so I can keep track of what 
their old info was, a bit like the system a wiki uses.
So for example I could have 3 lines with the member code 'm00025'.
This is why I would need to be able to combine : "SELECT 
a.message,a.date,b.surname,b.name,b.title FROM messages a JOIN info b ON 
a.from=b.code WHERE a.id='28'"
and "ORDER BYnum DESC LIMIT 1"

I hope I've been clear enough ... thanks in advance :)

Richard






Thread
help with a query...Richard28 Dec
  • Help with query, (question simplified as last mail was very complicatedto understand :))Richard29 Dec
    • Re: Help with query, (question simplified as last mail was very complicatedto understand :))Richard30 Dec
      • RE: Help with query, (question simplified as last mail was verycomplicated to understand :))Martin Gainty30 Dec