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

Sorry about my last email which was long and not clear.
This is what I want to do

Join two tables on "code table1" = "code table3" where messageid = for
example 28

table 1 contains :

message from messageid
------------------------------------------
message1 | code1 | 28
message2 | code1 | 28
message3 | code1 | 28
message4 | code1 | 29


table 2 contains

name | code | num
--------------------------------------
name1 | code2 | 1
name2 | code1 | 2
name3 | code1 | 3

If I do :
SELECT a.message,,b.name FROM table1 a JOIN table2 b ON a.code=b.code
WHERE a.id='28'

I get :
message | name
---------------------------
message1 | name2
message2 | name2
message3 | name2
message1 | name3
message2 | name3
message3 | name3


But all I want to get is :

message | name
---------------------------
message1 | name3
message2 | name3
message3 | name3

If I do :
SELECT * FROM table2 WHERE code = 'code1' ORDER BY num DESC LIMIT 1

I get :

name | code | num
--------------------------------------
name3 | code1 | 3

I now need to somehow combine the two to get :

message | name
---------------------------
message1 | name3
message2 | name3
message3 | name3

Of course I have simplified everything down to the minimum

Thanks in advance,

Richard

Richard a écrit :
> Sorry about my last email which was long and not clear.
> This is what I want to do
>
> Join two tables on "code table1" = "code table3" where messageid = for
> example 28
>
> table 1 contains :
>
> message from messageid
> ------------------------------------------
> message1 | code1 | 28
> message2 | code1 | 28
> message3 | code1 | 28
> message4 | code1 | 29
>
>
> table 2 contains
>
> name | code | num
> --------------------------------------
> name1 | code2 | 1
> name2 | code1 | 2
> name3 | code1 | 3
>
> If I do :
> SELECT a.message,,b.name FROM table1 a JOIN table2 b ON a.code=b.code
> WHERE a.id='28'
>
> I get :
> message | name
> ---------------------------
> message1 | name2
> message2 | name2
> message3 | name2
> message1 | name3
> message2 | name3
> message3 | name3
>
>
> But all I want to get is :
>
> message | name
> ---------------------------
> message1 | name3
> message2 | name3
> message3 | name3
>
> If I do :
> SELECT * FROM table2 WHERE code = 'code1' ORDER BY num DESC LIMIT 1
>
> I get :
>
> name | code | num
> --------------------------------------
> name3 | code1 | 3
>
> I now need to somehow combine the two to get :
>
> message | name
> ---------------------------
> message1 | name3
> message2 | name3
> message3 | name3
>
> Of course I have simplified everything down to the minimum
>
> Thanks in advance,
>
> Richard
>
>
As I have had no answer I presume that what I want to do is not possible
or my question is not well explained. Anyhow I've rethought the system
so I do not need to keep members information and now instead of adding a
new entry I will now just change the existing one. I won't keep old
members information in the database but I'll still have the database
daily backups if I need the old information.

the simple answer is and b.name='name3' ?Bon ChanceMartin______________________________________ ________Disclaimer and confidentiality noteEverythingin this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.> Date: Sun, 30 Dec 2007 13:54:32 +0100> From: mysql_list@ghz.fr> To: mysql@lists.mysql.com> Subject: Re: with query, (question simplified as last mail wasvery complicated to understand )> > Richard a écrit :> > Sorry about my last email which was long and not clear.> > This is what I want to do> >>> Join two tables on "code table1" = "code table3" where messageid = for > > example 28> >> > table 1 contains :> >> > message from messageid> > ------------------------------------------> > message1 | code1 | 28> > message2 | code1 | 28> > message3 | code1 | 28> > message4 | code1 | 29> >> >> > table 2 contains> >> > name | code | num> > --------------------------------------> > name1 | code2 | 1> > name2 | code1 | 2> > name3 | code1 | 3> >> > If I do :> > SELECT a.message,,b.name FROM table1 a JOIN table2 b ON a.code=b.code > > WHERE a.id='28'> >> > I get :> > message | name> > ---------------------------> > message1 | name2> > message2 | name2> > message3| name2> > message1 | name3> > message2 | name3> > message3 | name3> >> >>> But all I want to get is :> >> > message | name> > ---------------------------> > message1 | name3> > message2 | name3> > message3 | name3> >> > IfI do :> > SELECT * FROM table2 WHERE code = 'code1' ORDER BY num DESC LIMIT 1> >> > I get :> >> > name | code | num> > --------------------------------------> > name3 | code1 | 3> >> > I now need to somehow combine the twoto get :> >> > message | name> > ---------------------------> > message1 |name3> > message2 | name3> > message3 | name3> >> > Of course I have simplified everything down to the minimum > >> > Thanks in advance,> >> > Richard> >> >> As I have had no answer I presume that what I want to do is not possible > or my question is not well explained. Anyhow I've rethought the system > so I do not need to keep members information and now instead of adding a > new entry I will now just change the existing one. I won't keep old > members information in the database but I'll still have the database > daily backups if I need the old information.> > -- > MySQL General Mailing List> For list archives: http://lists.mysql.com/mysql> To unsubscribe: http://lists.mysql.com/mysql?unsub=mgainty@hotmail.com>
__________________________________________________ _______________
The best games are on Xbox 360. Click here for a special offer on an Xbox 360 Console.
http://www.xbox.com/en-US/hardware/wheretobuy/