From: Peter Brawley Date: August 2 2007 7:22pm Subject: Re: Joining question List-Archive: http://lists.mysql.com/mysql/208394 Message-Id: <46B22ED8.50100@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="------------040501020609030407070603" --------------040501020609030407070603 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit 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 >>>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>> >>>>> >>>>> >>>> >>> >>> >>> > > > --------------040501020609030407070603--