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 -, 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 -, 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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
