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