Re: Conflicting results using PHP/Mysql

Thanks to Kevin, who yesterday emailed me with a solution to my JOIN problem. Thanks to all who helped me see the wood for the trees. George ----- Original Message ----- From: "Doug Thompson" <dthompson@brickbarn.com> To: "George Pitcher" <fmdev@yahoo.co.uk>; <mysql@lists.mysql.com>; <php-db@lists.php.net> Sent: Monday, March 18, 2002 9:20 PM Subject: Re: Conflicting results using PHP/Mysql > Your base table is transactions. I assume you want to do a LEFT JOIN. > I think you might see an improvement if "transactions" were consistently named on the left side of the equalities as: > > $Itemlistquery.= "transactions.Pdownload "; > $Itemlistquery.= "from bib_extract,scanrates,transactions where "; > $Itemlistquery.= "(transactions.CourseID = '$Course_ID' and "; > $Itemlistquery.= "transactions.ExtractID=bib_extract.E_ID and "; > $Itemlistquery.= "transactions.finrate=scanrates.finrate) "; > > > On Mon, 18 Mar 2002 10:58:02 -0000, George Pitcher wrote: > > >It may be that my query is restricting the data retrieval but I cannot see > >how. > > > >If I look at the data I am playing with, I have 3 tables and I want to show > >transactions from a certain course. I also want to show related information > >from the bib_extract table and the scanrates (prices) table. > > > >I've checked the data in these tables and nothing is odd. Maybe there is a > >different way to build my query so that it displays the full set of records. > > > >Any suggestions? > > > >George > > > >----- Original Message ----- > >From: "Doug Thompson" <dthompson@brickbarn.com> > >To: "George Pitcher" <fmdev@yahoo.co.uk>; <mysql@lists.mysql.com>; > ><php-db@lists.php.net> > >Sent: Wednesday, March 13, 2002 5:59 PM > >Subject: Re: Conflicting results using PHP/Mysql > > > > > >> I would guess it's because only three records match the ANDed tests in the > >WHERE clause (last 3 lines). > >> > >> Isn't that what you intended? > >> > >> Doug > >> > >> > >> On Wed, 13 Mar 2002 16:08:43 -0000, George Pitcher wrote: > >> > >> >Hi all, > >> > > >> >Posted this yesterday and got no response. Trying again today. > >> > > >> >I'm having a small problem with a biggish query. > >> > > >> >The query: > >> > > >> >$Itemlistquery= "select [a whole load of fields from 3 tables] ending > >with > >> >"; > >> >$Itemlistquery.= "transactions.Pdownload "; > >> >$Itemlistquery.= "from bib_extract,scanrates,transactions where "; > >> >$Itemlistquery.= "(transactions.CourseID = '$Course_ID' and "; > >> >$Itemlistquery.= "bib_extract.E_ID=transactions.ExtractID and "; > >> >$Itemlistquery.= "scanrates.finrate=transactions.finrate) "; > >> > > >> >The problem: > >> > > >> >If I do a simple count of transactions.CourseID='$Course_ID' I get 18 > >(for a > >> >particular course) and the above query only displays 3 results. > >> > > >> >Any suggestions? I didn't want to clog the list with the whole query but > >I > >> >can if it's necessary. > >> > > >> >George, in Edinburgh > > > > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <mysql-thread103347@lists.mysql.com> > To unsubscribe, e-mail <mysql-unsubscribe-fmdev=yahoo.co.uk@lists.mysql.com> > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php