Tim

I,m trying

SELECT _COUNT(1)  FROM Emails
RIGHT OUTER JOIN Mailboxes MB ON MB._BoxNo = _fkMailBoxes
RIGHT OUTER JOIN emReadStatus RS ON  RS._fkMailBoxes = _fkMailBoxes
WHERE
RS._fkUsers IS NULL
AND
RS._fkMailBoxes IS NULL
AND
RS._fkEMails IS NULL
AND
MB._Monitored = TRUE

and get

ElevateDB Error #700 An error was found in the statement at line 1 and column 78 (Invalid expression  found, this table is the target of multiple join conditions)

I've come up with a workround which is pretty nippy

SELECT
(SELECT COUNT(1)
FROM Emails
JOIN MailBoxes MB ON MB._BoxNo = _fkMailBoxes
WHERE MB._Monitored = TRUE AND _fkUsers = 'RL')
-
(SELECT COUNT(1) FROM emReadStatus WHERE _fkUsers = 'RL')
FROM Emails
RANGE 1 TO 1

so I'm not unhappy but I'd like to know how to do it with joins.

I'd also like to ask if the execution plan for these selects with subselects will be fully operational in 2.0whatever


Roy Lambert

Roy,

<< ElevateDB Error #700 An error was found in the statement at line 1 and
column 78 (Invalid expression found, this table is the target of multiple
join conditions) >>

This is correct - you can't execute such a query, just like you can't
execute this:

SELECT *
FROM customer LEFT OUTER JOIN orders ON...
LEFT OUTER JOIN orders ON...

which is effectively the same thing.  With ROJs, EDB simply flips them
around to be LOJs.

<< so I'm not unhappy but I'd like to know how to do it with joins. >>

You can't do such a query with EDB using joins, at least not the way that
you have it constructed.  Perhaps you could give me an idea of what you're
trying to do ?

<< I'd also like to ask if the execution plan for these selects with
subselects will be fully operational in 2.0whatever >>

It's on the list.  That's about all I can tell you at this point.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>This is correct - you can't execute such a query

Fine now I know. <insert tongue in cheek> So maybe the error message should say - don't be stupid I can't do that <remove tongue from cheek>

>You can't do such a query with EDB using joins, at least not the way that
>you have it constructed. Perhaps you could give me an idea of what you're
>trying to do ?

Exactly what I achieved with the subselects

><< I'd also like to ask if the execution plan for these selects with
>subselects will be fully operational in 2.0whatever >>
>
>It's on the list. That's about all I can tell you at this point.

No problem - I only asked cos I wanted to optimise the query I build. Running the two sub queries gave me that info

Roy Lambert