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

[David Portas]

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

--
David Portas
------------
Please reply only to the newsgroup
--

[Mahmood]

"David Portas" <RE****************************@acm.org> wrote in message
news:2v********************@giganews.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,
LastPaymentAmount int,
LastPaymentDate Datetime )
Create Table Payments (
PaymentID int,
CustomerID int,
Amount int,
PaymentDate Datetime )

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

thx

[David Portas]

> I couldnt use the max function for this obviously.

Why not? My query should still work.

If you want a query that uses a join then try this:

SELECT C.*
FROM Customers AS C
LEFT JOIN
(SELECT customerid, MAX(paymentdate) AS lastpaymentdate
FROM Payments
GROUP BY customerid) AS P
ON C.customerid=P.customerid
AND C.lastpaymentdate=P.lastpaymentdate
WHERE C.lastpaymentdate IS NOT NULL
AND P.customerid IS NULL

This has a possible advantage over the correlated subquery version. It will
include Customers who have a Lastpaymentdate but don't have any non-NULL
dates in the Payments table.

--
David Portas
------------
Please reply only to the newsgroup
--

[David Portas]

Oops, you wanted the *correct* values:

SELECT C.*
FROM Customers AS C
JOIN
(SELECT customerid, MAX(paymentdate) AS lastpaymentdate
FROM Payments
GROUP BY customerid) AS P
ON C.customerid=P.customerid
AND C.lastpaymentdate=P.lastpaymentdate

--
David Portas
------------
Please reply only to the newsgroup
--

[Mahmood]

"David Portas" <RE****************************@acm.org> wrote in message
news:4e********************@giganews.com...

Oops, you wanted the *correct* values:

SELECT C.*
FROM Customers AS C
JOIN
(SELECT customerid, MAX(paymentdate) AS lastpaymentdate
FROM Payments
GROUP BY customerid) AS P
ON C.customerid=P.customerid
AND C.lastpaymentdate=P.lastpaymentdate

--
David Portas
------------
Please reply only to the newsgroup
--

I want the LastPaymentAmount of Customers table to be compaired with the
Amount of the last payment in Payments table.
Ofcourse the last payment is the one with the maximum PaymentDate but the
Amount fields should be compared in both tables to see if they are equal.

[David Portas]

You didn't include any keys or constraints with your DDL but I'll have to
assume that (customerid, paymentdate) is unique in Payments - otherwise how
will you define which payment is the latest?

SELECT C.customerid, C.lastpaymentamount, C.lastpaymentdate
FROM
(SELECT customerid,
MAX(paymentdate) AS paymentdate
FROM Payments
GROUP BY customerid) AS P1
JOIN Payments AS P2
ON P1.customerid = P2.customerid
AND P1.paymentdate = P2.paymentdate
JOIN Customers AS C
ON P2.customerid = C.customerid
AND P2.paymentdate = C.lastpaymentdate
AND P2.amount = C.lastpaymentamount

--
David Portas
------------
Please reply only to the newsgroup
--

[--CELKO--]

>> what query will bring me the customers whose last_payment_date in
the
customers table is not correct. <<

Your design is wrong; this is a computed value and you do not store
computed values in an RDBMS. Your Customers table should not exist at
all! And you do not have keys or anything that makes payments into a
table. Let's get the basics right:

CREATE TABLE Payments
(payment_id INTEGER NOT NULL PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES Customers
ON DELETE CASCADE
ON UPDATE CASCADE,
payment_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL);

Now create a VIEW which will always be correct:

CREATE VIEW CurrentPayments (payment_id, customer_id, payment_date)
AS
SELECT payment_id, customer_id, payment_date
FROM Payments AS P1
WHERE P1.payment_date
= (SELECT MAX(payment_date)
FROM Payments AS P2
WHERE P1.customer_id = P2.customer_id);

[Erland Sommarskog]

--CELKO-- (jo*******@northface.edu) writes:

Your design is wrong; this is a computed value and you do not store
computed values in an RDBMS.

Maybe in some small ideal model of a database you don't.

In real-world databases, you often find that you can save a lot of
cycles by storing computed data. For instance, if you have a transactions
table for a bank account, you probably want to store the balance on the
account after each transaction, because you will have several functions
in you system where you display this information. Having it computed
means that this function will run faster, and that the likelyhood that
they will show the wrong value because of bugs is less.

The one problem with storing computed values, is that if an underlying
value changes, the computed value changes too. But in the example I
just gave, this never happens due to business rules.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Joe Celko]

>> In real-world databases, you often find that you can save a lot of
cycles by storing computed data. <<

OLAP versus versus OLTP databases. What I found is that the summary
data is usually processed by many applications at the same time (i.e.
end of the month, end of the quarter, end of the year), so you can
materialize the VIEWs and share them at that point in time instead of
always updating a redundant permanent table.

Unfortunately, SQL Server is not designed with that kind of sharing in
mind; it is more of a mainframe, heavy batch processing system approach.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[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*********@yahoo.com>
wrote:

"David Portas" <RE****************************@acm.org> wrote in message
news:2v********************@giganews.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,
LastPaymentAmount int,
LastPaymentDate Datetime )
Create Table Payments (
PaymentID int,
CustomerID int,
Amount int,
PaymentDate Datetime )

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

thx