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