Thanks, but not really. I wish to get all the cases, even when there is no
Blurb in Preview. Your query only grabs cases where the Blurb in Preview
is missing by adding WHERE Preview.CASEID IS NULL. - Brian
- BLH
At 01:00 PM 1/25/2001 -0600, you wrote:
>Does this help?
>
>SELECT
> ArgumentCalendar.Docket
> , ArgumentCalendar.Date
> , DATE_FORMAT(ArgumentCalendar.Date, '%W, %M %e, %Y') AS formatted_date
> , CaseID.CASEID
> , CONCAT(Party1, ' v. ', Party2) AS name
> , Blurb
>
> FROM
> ArgumentCalendar
> , CaseID
> , Parties
>
> LEFT JOIN Preview ON Parties.CASEID = Preview.CASEID
>
> WHERE Preview.CASEID IS NULL
> AND CaseID.DocketNumber = ArgumentCalendar.Docket
> AND Parties.CASEID = CaseID.CASEID
> AND '20010125' <= ArgumentCalendar.Date
>
>
> > Hi. I have a query which works and is quick, but it misses a couple of
> > records.
> >
> > SELECT ArgumentCalendar.Docket
> > , ArgumentCalendar.Date
> > , CaseID.CASEID
> > , CONCAT(Party1, ' v. ', Party2) AS name
> > , Preview.Blurb
> > FROM ArgumentCalendar
> > , CaseID
> > , Parties
> > , Preview
> > WHERE CaseID.DocketNumber = ArgumentCalendar.Docket
> > AND Parties.CASEID = CaseID.CASEID
> > AND Preview.CASEID = CaseID.CASEID
> > AND '20001001' <= ArgumentCalendar.Date
> > AND ArgumentCalendar.Date <= '20010630;
> >
> > I needed to _also_ select the records which don't have a corresponding
> > record in Preview, so I threw in a left join:
> >
> > SELECT ArgumentCalendar.Docket
> > , ArgumentCalendar.Date
> > , DATE_FORMAT(ArgumentCalendar.Date, '%W, %M %e, %Y') AS formatted_date
> > , CaseID.CASEID
> > , CONCAT(Party1, ' v. ', Party2) AS name
> > , Blurb
> > FROM ArgumentCalendar
> > , CaseID
> > , Parties
> > LEFT JOIN Preview ON Preview.CASEID = Parties.CASEID
> > WHERE CaseID.DocketNumber = ArgumentCalendar.Docket
> > AND Parties.CASEID = CaseID.CASEID
> > AND '20010125' <= ArgumentCalendar.Date
> >
> > The second query works correctly with the left join, but now
> > mysql looks at
> > all 19000 records in Parties (according to EXPLAIN) and the query takes
> > about 15 seconds to execute. I have fiddled and read & fiddled
> > more, but I
> > have failed to speed up this query. If I change the order around or add
> > another left join I have removed the problem with the Parties table, but
> > then EXPLAIN says all 19000 CaseID records are being examined.
> > Without the
> > LEFT JOIN MySQL only looks at all the ArgumentCalendar & all the Preview
> > records, which are like 63 and 150 respectively.
> >
> > With the Left Join I select 61 records, without it 59 (which is
> > as expected
> > -- the issue is just speed).
> >
> > I am using MySQL 3.22.25.
> >
> > Am I screwed or is there some syntactic SQL point I am missing?
> >
> > - BLH
> >
> >
> > ---------------------------------------------------------------------
> > 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
---------------------------------------------------------------------
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
