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
