List: General Discussion « Previous MessageNext Message » From: Richard Date: December 30 2007 12:54pm Subject: Re: Help with query, (question simplified as last mail was very complicated to understand :)) View as plain text
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 :)
>
>
> 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.