Transform SubSelect in OUTER JOIN

[Tom Schindl]

Hi,

maybe I'm simply to dump but I could not transform this SQL-Statment
which uses a Sub-select and create on that uses an OUTER JOIN.

--------------------------8<--------------------------
SELECT
p_id,
CONCAT( " hist. ", CONCAT( UPPER(p_surname), CONCAT( ',',
p_givenname ) ) )
FROM
persons p,
employee_contracts e
WHERE
p.delmark = 0
AND e.delmark = 0
AND e.ec_fk_person = p.p_id
AND p_is_employee = 1
AND e.ec_end_date <= NOW()
AND p_id NOT IN
(
SELECT DISTINCT
p_id
FROM
persons p,
employee_contracts e
WHERE
p.delmark = 0
AND e.delmark = 0
AND e.ec_fk_person = p.p_id
AND p_is_employee = 1
AND ( e.ec_end_date >= NOW() OR e.ec_end_date IS NULL )

)
--------------------------8<--------------------------

Thanks for your help. I cannot switch to mysql-4.1 and that's why I need
this working without sub-selects.

Tom

[Bill Karwin]

Tom Schindl wrote:

...I could not transform this SQL-Statment
which uses a Sub-select and create one that uses an OUTER JOIN.
....troublesome query omitted...

You might have already found a solution, since your original posting was
four days ago, but try something like this:

SELECT p.p_id, CONCAT( " hist. ", CONCAT( UPPER(p.p_surname), CONCAT( ',', p.p_givenname ) ) )

FROM persons AS p
INNER JOIN employee_contracts AS e
ON p.p_id = e.ec_fk_person
LEFT OUTER JOIN employee_contracts AS e2
ON (p.p_id = e2.ec_fk_person AND e2.delmark = 0
AND (e2.ec_end_date >= NOW() OR e2.ec_end_date IS NULL) )
WHERE p.delmark = 0 AND e.delmark = 0 AND p.p_is_employee = 1
AND e.ec_end_date <= NOW()
AND e2.ec_fk_person IS NULL;

Caveats: this is not tested;
also, it assumes that ec_fk_person is a NOT NULL field, so a NULL state
could only be true if the outer join had not been satisfied.

Regards,
Bill K.