Corrolated Subquery Across 2 Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Corrolated Subquery Across 2 Tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-12 : 09:22:57

Kimberly writes "I love your site. Here is a problem I believe this is a totally impossible problem to solve without resolving to temp tables or use of a cursor. Can you confirm? Here is the select problem I have been trying (unsuccessfully) to solve using subqueries:

I have a table CUSTOMERS_ORDER_T containing ID_CUSTOMER and ID_ORDER. Customers exist on this table with mutliple orders, and orders exist on this table with multiple customers. Here is a sample:



ID_CUSTOMER    ID_ORDER
1              1
1              2
2              1
2              3

I also have a table ORDERS_T containing ID_ORDER and DTE_ORDER_FUFILLED


ID_ORDER       DTE_ORDER_FUFILLED    
1              1997                   
2              1995
3              1999

Now here is what I need: I want a list of each customer along with the ID_ORDER for thier most recently fufilled order. For Example:


ID_CUSTOMER    ID_ORDER 
1              1
2              3

I have tried nesting subqueries to find the max DTE_ORDER_FUFILLED, but I can't get it to work in a corrolated fashion returning one row per ID_CUSTOMER. I can only get it to return the max date for the whole ID_ORDER table. If I play with it I am warned that I cannot have an aggregate function in a WHERE clause, that I must instead put it in the HAVING clause, but I'm not having much luck with that either. I hope you can figure this one out!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources