473,248 Members | 1,972 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,248 software developers and data experts.

Help using columns from Left Outer Join in query

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
5 8192
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Xenophobe | last post by:
I know this isn't a MySQL forum, but my question is related to a PHP project. I have two tables. table1 table2 "table1" contains 2 columns, ID and FirstName:
9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
3
by: Prem | last post by:
Hi, I am having many problems with inner join. my first problem is : 1) I want to know the precedance while evaluating query with multiple joins. eg. select Employees.FirstName,...
2
by: Preston Landers | last post by:
Hello all. I am trying to write a query that "just" switches some data around so it is shown in a slightly different format. I am already able to do what I want in Oracle 8i, but I am having...
28
by: stu_gots | last post by:
I have been losing sleep over this puzzle, and I'm convinced my train of thought is heading in the wrong direction. It is difficult to explain my circumstances, so I will present an identical...
4
by: thilbert | last post by:
All, I have a perplexing problem that I hope someone can help me with. I have the following table struct: Permission ----------------- PermissionId Permission
7
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins"...
8
by: Thomas R. Hummel | last post by:
Hello, I am currently working with a data mart. The business wants a view of the data that shows columns from the main table along with up to 6 codes from a related table (not related by the...
3
by: nico3334 | last post by:
I currently have a query that Joins 2 Tables (Table1 and Table2) using LEFT OUTER JOIN. Here is an example of that query: SELECT a.supply, a.state, b.cost FROM Table1 a LEFT...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.