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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
