List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 2 2007 7:22pm
Subject:Re: Joining question
View as plain text  
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
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>>
>
>
>

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