By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 2,021 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

Help using columns from Left Outer Join in query

P: n/a
Data related to the query I'm working on is structured such that
TableA and TableB are 1-many(optional). If an item on TableA has
children on TableB, I need to use the Max(tstamp) from Table B in a
condition, otherwise I need to use a tstamp from TableA (note:there
are additional tables and conditions for this query, but this problem
is based around these 2). I attempted having TableB (as B) "left
outer joined" to TableA, and a condition in the query that resembles:
Where B.Tstamp = (select MAX(tstamp) from TableB
where pkey = B.pkey)

However, the obvious problem, is that when B.Tstamp is Null, the
condition fails because the subselect fails. Is there a way I can get
the subselect to return a Null instead of failing, or a cleaner way to
be able to evaluate this condition and still handle when B.Tstamp is
Null and when it is not?

thanks in advance
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
with t1 (tstamp,pkey) as
(select max(tstamp),pkey from tableb group by pkey)
select a.pkey, coalesce(b.tstamp,a.tstamp)
from tableA a
left outer join t1 b
on b.pkey = a.pkey

will do what you want but may not perform well depending on table sizes.
If the timestamps on tableb are ALWAYS greater than tablea then:

select a.pkey, max(coalesce(b.tstamp,a.tstamp))
from tablea a
left outer join tableb b
on b.pkey = a.pkey
group by a.pkey

You may want to try a global temporary table with an index to get better
performance with the (first example) join. Check explains for different
approaches and see which yields the best performance.

Phil Sherman
Todd wrote:
Data related to the query I'm working on is structured such that
TableA and TableB are 1-many(optional). If an item on TableA has
children on TableB, I need to use the Max(tstamp) from Table B in a
condition, otherwise I need to use a tstamp from TableA (note:there
are additional tables and conditions for this query, but this problem
is based around these 2). I attempted having TableB (as B) "left
outer joined" to TableA, and a condition in the query that resembles:
Where B.Tstamp = (select MAX(tstamp) from TableB
where pkey = B.pkey)

However, the obvious problem, is that when B.Tstamp is Null, the
condition fails because the subselect fails. Is there a way I can get
the subselect to return a Null instead of failing, or a cleaner way to
be able to evaluate this condition and still handle when B.Tstamp is
Null and when it is not?

thanks in advance


Nov 12 '05 #2

P: n/a
I think when B.Tstamp is NULL, your subselect returns NULL.
But, "NULL = NULL" is UNKNOWN(not TRUE), then the row will not be
included in result set.
Another consideration is that it might be better to correlate with A,
if possible.

So, my idea is the following:
WHERE (B.tstamp
= (SELECT MAX(tstamp)
FROM TableB
WHERE pkey = A.pkey
)
OR
B.tstamp IS NULL
)
Nov 12 '05 #3

P: n/a
This is another idea.
SELECT ...
, COALESCE(B.tstamp, A.tstamp)
FROM TableA A
LEFT OUTER JOIN
TABLE
(SELECT ...
FROM TableB B
WHERE B.pkey = A.pkey
AND B.tstamp
= (SELECT MAX(tstamp)
FROM TableB Bm
WHERE Bm.pkey = A.pkey
)
) B
ON 0 = 0

If you could select small number of rows in TableA, this would be perform well.
Nov 12 '05 #4

P: n/a
This worked great. Thanks!

If the timestamps on tableb are ALWAYS greater than tablea then:

select a.pkey, max(coalesce(b.tstamp,a.tstamp))
from tablea a
left outer join tableb b
on b.pkey = a.pkey
group by a.pkey

Nov 12 '05 #5

P: n/a
AK
to********@yahoo.com (Todd) wrote in message news:<28*************************@posting.google.c om>...
Data related to the query I'm working on is structured such that
TableA and TableB are 1-many(optional). If an item on TableA has
children on TableB, I need to use the Max(tstamp) from Table B in a
condition, otherwise I need to use a tstamp from TableA (note:there
are additional tables and conditions for this query, but this problem
is based around these 2). I attempted having TableB (as B) "left
outer joined" to TableA, and a condition in the query that resembles:
Where B.Tstamp = (select MAX(tstamp) from TableB
where pkey = B.pkey)

However, the obvious problem, is that when B.Tstamp is Null, the
condition fails because the subselect fails. Is there a way I can get
the subselect to return a Null instead of failing, or a cleaner way to
be able to evaluate this condition and still handle when B.Tstamp is
Null and when it is not?

thanks in advance


I'd rewrite the query using a table expression instead:

FROM
....
(SELECT TABLEA.TABLEA_PK, MAX(TABLEB.TSTAMP) TSTAMP
FROM TABLEA JOIN TABLEB ON TABLEA.TABLEA_PK = TABLEB.TABLEA_PK
GROUP BY TABLEA.TABLEA_PK
UNION ALL
SELECT TABLEA.TABLEA_PK, TABLEA.TSTAMP
FROM TABLEA
WHERE NOT EXISTS(SELECT * FROM TABLEB WHERE TABLEA.TABLEA_PK =
TABLEB.TABLEA_PK)
)MY_EXPRESSION,
....
WHERE TABLEA.TABLEA_PK = MY_EXPRESSION.TABLEA_PK
AND TABLEC.SOME_COLUMN = MY_EXPRESSION.TSTAMP

alternatively, one could use a CASE expression:
WHERE
....
TABLEC.SOME_COLUMN = CASE WHEN EXISTS(
SELECT * FROM TABLEB WHERE TABLEA.TABLEA_PK = TABLEB.TABLEA_PK)
THEN (SELECT MAX(TABLEB.TSTAMP) FROM TABLEB WHERE TABLEA.TABLEA_PK =
TABLEB.TABLEA_PK
ELSE TABLEA.TSTAMP
END
....

Good luck!
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.