List:General Discussion« Previous MessageNext Message »
From:Richard Date:August 2 2007 7:10pm
Subject:Re: Joining question
View as plain text  
Hi
My guess is I have not described my problem well enough then ...

Here is an example of table one :

MESSAGE      |   REFERENCE     |       SENDER
----------------------------------------------------
message1 text   |   000005               |      M000001
message2 text   |   000010               |      M000015
message3 text   |   000005               |      M000004
message4 text   |    000005              |       M000001

And of table 2 :

MEMBERID    |      NAME
----------------------------------
M000001         |       Mr Smith
M000002         |       Mr Jones
M000003         |       Mrs Thomson
M000004         |       Mrs Harisson


So I need the query to give me :

for $reference=000005

The following array :

MESSAGE          |     NAME
---------------------------------------
"message1 text"    |     "Mr Smith"
"message3 text     |       "Mrs Harrison"
"message4 text"    |     "Mr Smith"

Is this clearer ?

Thanks :)


Peter Brawley a écrit :
> Richard,
>
> This is elementary---you most definitely do not need to do it with PHP 
> code. Given tables  messages(senderid, message, reference) and 
> senders(senderid, name, address), this query
>
> SELECT m.message, m.reference, s.name
> FROM messages m
> JOIN senders s ON m.senderid=s.senderid
> WHERE m.reference='$reference';
>
> retrieves what you say you want. If it "does not work", there is a 
> mistake in the description of the problem.
>
> PB
>
> -----
>
> Richard wrote:
>> I've tried it and it does not work,
>>
>> the problem is that there needs to be 1 table1 row for each table2 
>> row, and  table 1 is the message list and the table two is the 
>> members information list.
>>
>> So I need the same row to be joined to all the message rows with the 
>> same senderid ...
>>
>> I guess I will have to manage this with the php code and not get the 
>> result directly from the sql query; thanks anyway !
>>
>> Peter Brawley a écrit :
>>> That query will give one row per table1 row matching your WHERE 
>>> clause, with matched row from table2. Is that what you want?
>>>
>>> PB
>>>
>>> Richard wrote:
>>>> Thanks,
>>>> I think I have found the correct syntax in a book I've got :
>>>>
>>>> SELECT A.message,B.name
>>>> FROM table1 A
>>>> JOIN table2 B ON A.senderid=B.senderid
>>>> WHERE A.reference="$reference";
>>>>
>>>> I am just about to test this code, however I've now got doubts that 
>>>> this will work as table1 contains more than one row with eache 
>>>> userid whereas table two only contains one.... (table one is the 
>>>> messages and table2 contains user information.
>>>>
>>>> could this work or should I try another aproach?
>>>>
>>>> Thanks :)
>>>>
>>>> Richard
>>>>
>>>> Peter Brawley a écrit :
>>>>> Richard,
>>>>>
>>>>> In ...
>>>>>
>>>>> SELECT t1.message, t1.reference, t2.name
>>>>> FROM tbl1 t1
>>>>> JOIN tbl2 ON t1.senderid=t2.senderid
>>>>> WHERE t1.reference = '$reference';
>>>>>
>>>>> t1 and t2 are table aliases used to simplify table references.
>>>>>
>>>>> The comma join syntax you cite will sometimes work, and sometimes 
>>>>> will not. It's not sufficiently reliable for general use. Explicit 
>>>>> join syntax is therefore always preferred.
>>>>>
>>>>> The WHERE clause cannot precede the JOIN clause.
>>>>>
>>>>> PB
>>>>>
>>>>> -----
>>>>>
>>>>> Richard wrote:
>>>>>> Yes that looks like what I am looking for.
>>>>>>
>>>>>> could you explain what "FROM tabl1 t1" means ...I've seen "FROM 
>>>>>> tbl1,tbl2" before but I haven't come accross seperation with a 
>>>>>> space yet ...
>>>>>>
>>>>>> I didn't explain everything I guess what I am looking for is :
>>>>>>
>>>>>> SELECT t1.message,t2.name
>>>>>> FROM tbl1 t1
>>>>>> WHERE t1.reference='$reference'
>>>>>> JOIN tbl2 ON t1.senderid=t2.senderid;
>>>>>>
>>>>>>
>>>>>> But I don't quite understand the t1. or the t2. is it just a way
> 
>>>>>> to make it more understandable?
>>>>>>
>>>>>> Could I do :
>>>>>>
>>>>>> SELECT message,name
>>>>>> FROM table1
>>>>>> WHERE reference='$reference'
>>>>>> JOIN table2 ON senderid=senderid2;
>>>>>>
>>>>>> if this is wrong then I don't understand why there is no 
>>>>>> reference to t2 in your code ("FROM tbl1 t1" but nothing for t2)
>>>>>>
>>>>>> Sorry about these questions, I'm beginning with mysql !
>>>>>>
>>>>>> Peter Brawley a écrit :
>>>>>>> Richard
>>>>>>>
>>>>>>> >I have table1 containing : message, senderid, reference
>>>>>>> >and table2 contains: senderid, name, address
>>>>>>> >I want to do a query that gives me : message, reference
> and 
>>>>>>> name ...
>>>>>>>
>>>>>>> Do you mean ...
>>>>>>>
>>>>>>> SELECT t1.message, t1.reference, t2.name
>>>>>>> FROM tbl1 t1
>>>>>>> JOIN tbl2 ON t1.senderid=t2.senderid;
>>>>>>>
>>>>>>> PB
>>>>>>>
>>>>>>> -----
>>>>>>>
>>>>>>> Richard wrote:
>>>>>>>> Hello, I'm not sure if I can use union here or what
> syntax I 
>>>>>>>> should use. I will simplify the tables to only include
> the 
>>>>>>>> necessary information.
>>>>>>>>
>>>>>>>> I have table1 containing : message, senderid, reference
>>>>>>>> and table2 contains: senderid, name, address
>>>>>>>>
>>>>>>>> I want to do a query that gives me : message, reference
> and 
>>>>>>>> name ...
>>>>>>>>
>>>>>>>> Is this possible with one query?
>>>>>>>>
>>>>>>>>
>>>>>>>> Thanks !
>>>>>>>>
>>>>>>>> Richard
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>>

Thread
Joining questionRichard2 Aug
  • Re: Joining questionPeter Brawley2 Aug
Re: Joining questionPeter Brawley2 Aug
  • Re: Joining questionRichard2 Aug
    • Re: Joining questionPeter Brawley2 Aug
      • Re: Joining questionRichard2 Aug
        • Re: Joining questionPeter Brawley2 Aug
          • Re: Joining questionRichard2 Aug