|
|
Posted by Erland Sommarskog on 10/02/13 11:50
TGEAR (ted_gear@hotmail.com) writes:
> 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
This should probably be
SELECT i.*, h.dtbegin, h.price
FROM ItemLookUp i
LEFT JOIN ItemSTDPriceHistory h
ON i.index_id = h.ItemLookUpID
AND h.dtbegin = (SELECT MAX(h2.dtbegin)
FROM ItemSTDPriceHistory h2
WHERE h2.ItemLookUpID = h.ItemLookupID
ORDER BY i.itmnumber, i.descript, h.dtbegin, h.price DESC
1) You must correlate the subquery with the outer instance of
ItemStdPriceHistory.
2) If you have a LEFT JOIN, and then have a WHERE condition which includes
the right-hand side of the table, you have effectively made the outer
join an inner join, as you filter all rows where the columns evaluates
to NULL on the right. (FROM - JOIN are evaluated before WHERE.)
> so here is the sample data
>
> h.index_id h.ItemLookupID h.dtbegin h.price
> i.itmnumber i.descript
> --------------------------------------------------------------------------
----------------------------------------------------
>
> 1 4 4/2/2006 1500 000001
Since you did not take the occassion to practice INSERT statements, the
query above is not tested.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|