Roy,

<< You missed the

>>

No, I saw it.  I just didn't think that it changed the context of the
sentence.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>No, I saw it. I just didn't think that it changed the context of the
>sentence.

I read it as a joke, and since I have that style of humor I would have thought so even without the smiley.

Roy Lambert

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:319F16FB-FFD3-41B3-9E8F-0ABF4F5A2A6C@news.elevatesoft.com...
>
> No, I saw it.

Tilt.
Rita

Tim


It works now in b9 - just thought I'd say.

Roy Lambert

Roy,

<< It works now in b9 - just thought I'd say. >>

Thanks for the update.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

I don't know if this should be in a new thread but the query I sent to Roy
(see below) which produced 0 records appears to be a problem with join
optimization.  If I add nojoinoptimize it returns 351 "duplicate" records.
I tried the same query replacing the dervived table with a "physical table"
and got the same results.  This was tested with b9.

John

SELECT DISTINCT c._id,_fkContacts,_Surname, _Forename,_fkCompanies,_fkSites
FROM Career C
JOIN Contacts C1 ON C._fkContacts=Contacts._ID
JOIN
(SELECT _Surname, _Forename,_fkCompanies,_fkSites,count(*) as cnt
FROM Career
JOIN Contacts C ON _fkContacts = C._ID
GROUP BY _Surname, _Forename,_fkCompanies,_fkSites
HAVING cnt > 1) t1
ON C1._Surname=t1._Surname AND C1._Forename= t1._Forename AND
C._fkCompanies=t1._fkCompanies AND
C._fkSites=t1._fkSites order by _surname,_forename

John


Congratulations on ferreting that out. I would literally never have thought of adding nojoinoptimize.


Roy Lambert

John,

<< I don't know if this should be in a new thread but the query I sent to
Roy (see below) which produced 0 records appears to be a problem with join
optimization.  If I add nojoinoptimize it returns 351 "duplicate" records. I
tried the same query replacing the dervived table with a "physical table"
and got the same results.  This was tested with b9. >>

Could you please send me what you're using on your end (database catalog and
tables) ?

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


You should be able to download the script to create them from the binaries "Data for John Hay"

Roy Lambert

Tim

As Roy wrote, the data is in the binaries.  The only additional test I did
was to create a table with

CREATE TABLE crtemp AS SELECT _Surname,
_Forename,_fkCompanies,_fkSites,count(*) as cnt
FROM Career
JOIN Contacts C ON _fkContacts = C._ID
GROUP BY _Surname, _Forename,_fkCompanies,_fkSites
HAVING cnt > 1

I then ran the query the query below which got 0 records.

SELECT DISTINCT c._id,_fkContacts,_Surname, _Forename,_fkCompanies,_fkSites
FROM Career C
JOIN Contacts C1 ON C._fkContacts=Contacts._ID
JOIN crtemp t1
ON C1._Surname=t1._Surname AND C1._Forename= t1._Forename AND
C._fkCompanies=t1._fkCompanies AND
C._fkSites=t1._fkSites order by _surname,_forename

Cheers

John