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