Question about JOIN syntax

[Mahmood]

what is the syntax to join a table with the result of antoher query.

For example i have two tables

Create Table Customers (
CustomerID int,
LastPaymentDate Datetime )
Create Table Payments (
PaymentID int,
CustomerID int,
PaymentDate Datetime )

What query will bring me the customers whose lastpaymentdate in the
customers table is not correct.

That can only be checked by comparing it with the max paymentdate for each
customer in the payments table.

I want this to be done by ansi standard sql. Not using any specific feature
of sql server.

thx

[Anith Sen]

>> this is a computed value and you do not store computed values in an
RDBMS. <<

May I know why so? Snapshots and stored joins, as recommended by the
relational model are typical examples where computed values are stored in an
RDBMS.

In SQL Server, you can materialize computed columns and views by physically
clustering them and I don't see why this should not be done, when required.

--
- Anith
( Please reply to newsgroups only )

[Ellen K.]

I agree with Joe. (Not that he needs my agreement!) Your customers
table should hold information that describes each customer. The
customer's payment history doesn't belong there. You can have a view
that holds each customer's latest payment if that's something you use
a lot. And why do you have a table for payments? You should have a
table for transactions, of which payments are only one flavor.

On Fri, 2 Jan 2004 13:45:29 +0500, "Mahmood" <a2*********@ya hoo.com>
wrote:

"David Portas" <RE************ *************** *@acm.org> wrote in message
news:2v******* *************@g iganews.com...

SELECT *
FROM Customers AS C
WHERE lastpaymentdate <>
(SELECT MAX(paymentdate )
FROM Payments AS P
WHERE P.customerid=C. customerid)

oh may be my example was not the best!

what if I have

Create Table Customers (
CustomerID int,
LastPaymentAmo unt int,
LastPaymentDat e Datetime )
Create Table Payments (
PaymentID int,
CustomerID int,
Amount int,
PaymentDate Datetime )

and I want to see if the LastPaymentAmou nt field holds correct values.
I couldnt use the max function for this obviously.

thx