469,963 Members | 1,885 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,963 developers. It's quick & easy.

Transform SubSelect in OUTER JOIN

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
Jul 23 '05 #1
1 1447
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.
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Matt | last post: by
1 post views Thread by Magnus | last post: by
4 posts views Thread by dtwalter | last post: by
1 post views Thread by Marco Lazzeri | last post: by
2 posts views Thread by Morten K. Poulsen | last post: by
6 posts views Thread by Sebastien | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.