|
|
Posted by Hugo Kornelis on 10/02/44 11:50
On 14 Jun 2006 16:19:29 -0700, TGEAR wrote:
>I think I should state more clearly. Sorry for the mess.
>
>SELECT i.*, h.dtbegin, h.price
>FROM ItemLookUp i LEFT OUTER JOIN
> ItemSTDPriceHistory h ON i.index_id =
>h.ItemLookUpID
>WHERE (h.dtbegin =
> (SELECT MAX(dtbegin)
> FROM ItemSTDPriceHistory))
>ORDER BY i.itmnumber, i.descript, h.dtbegin, h.price DESC
Hi TGEAR,
The WHERE clause effectively turns the outer join back into an inner
join. Remember that rows retained by the outer join get a bunch of NULLs
for the columns in the other table (the one alised as h in your query).
So any WHERE cllause that involves those column will automatically
remove them again, since a comparison against NULL can never evaluate to
true. You can fix this problem by moving the dtbegin test from the WHERE
clause to the ON clause.
Also, you need to correlate the subquery against the main query. As yoou
have written the query, only ItemSTDPriceHistory with a date of 9/6/2006
(BTW, is that June 9th or September 6th?) will be selected, since that
is the MAX(dtbegin) in that table.
Try this query instead:
SELECT i.*, -- NEVER USE SELECT * IN PRODUCTION CODE !!!!!!
h.dtbegin, h.price
FROM ItemLookUp i
LEFT JOIN ItemSTDPriceHistory h
ON i.index_id = h.ItemLookUpID
AND h.dtbegin = (SELECT MAX(dtbegin)
FROM ItemSTDPriceHistory AS h2
WHERE h2.ItemLookUpID = i.index_id)
ORDER BY i.itmnumber, i.descript, h.dtbegin, h.price DESC
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|