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