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

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