Maybe something like this (which I cannot test):
SELECT
c.id,
c.name,
i.*
FROM
customer c
LEFT OUTER JOIN
(
-- Get max (most recent) id (assuming pkey) of
-- each invoice as grouped by client.
SELECT
i2.customer_id
MAX( i2.id ) AS invoice_id
FROM
invoice i2
GROUP BY
i2.customer_id
) AS inv
ON
c.id = inv.customer_Id
LEFT OUTER JOIN
invoice i
ON
inv.invoice_id = i.id
We create an interim table that has JUST the customer id and MAX invoice
Id. Then we join that to customers, and then join that to invoices (only
were the max id = the invoice id).
......................
Ben Nadel
Certified Advanced ColdFusion MX7 Developer
www.bennadel.com
Need ColdFusion Help?
www.bennadel.com/ask-ben/
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 04, 2007 8:58 AM
To: CF-Talk
Subject: SQL Query Help
having brain fart here ... ughh
scenario: customer table, invoice table
I'd like to do a query to list the last invoice for each customer.
Can someone point me in the right direction please?
TIA,
Jenny
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 &
Flex 2
Free Trial
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274517
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
