Thanks!!
I must have a problem with my code then !! I will check it out then.
Peter Brawley a écrit :
> DROP TABLE IF EXISTS messages;
> CREATE TABLE messages( message text, reference char(10), sender char(10));
> INSERT INTO MESSAGES VALUES
> ('message1 text' , '000005' , 'M000001'),
> ('message2 text' , '000010' , 'M000015'),
> ('message3 text' , '000005' , 'M000004'),
> ('message4 text' , '000005' , 'M000001');
>
> DROP TABLE IF EXISTS senders;
> CREATE TABLE senders( memberid char(10), name char(20) );
> INSERT INTO SENDERS VALUES
> ('M000001' , 'Mr Smith' ),
> ('M000002' , 'Mr Jones' ),
> ('M000003' , 'Mrs Thomson' ),
> ('M000004' , 'Mrs Harisson' );
>
> SELECT m.message, m.reference, s.name
> FROM messages m
> JOIN senders s ON m.sender=s.memberid
> WHERE m.reference='000005';
> +---------------+-----------+--------------+
> | message | reference | name |
> +---------------+-----------+--------------+
> | message1 text | 000005 | Mr Smith |
> | message4 text | 000005 | Mr Smith |
> | message3 text | 000005 | Mrs Harisson |
> +---------------+-----------+--------------+
>
> PB
>
> -----
>
> Richard wrote:
>> 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
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>>
>>
>>
>>
