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 :)

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