From: Richard Date: August 2 2007 7:23pm Subject: Re: Joining question List-Archive: http://lists.mysql.com/mysql/208395 Message-Id: <46B22F4C.4090405@ghz.fr> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit 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 >>>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>> >>>>>> >>>>>> >>>>> >>>> >>>> >>>> >> >> >>