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" <[EMAIL PROTECTED]> To: "George Pitcher" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > >To: "George Pitcher" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; > ><[EMAIL PROTECTED]> > >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 <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
