November 29, 2007 at 1:02 pm
I was given the statement below to match a list of customers against their most recent invoices. It works very well except I want to add the customer's name to the results as well.
The statement is:
SELECT TOP 100 PERCENT
customer_id,
invoice_no,
invoice_date
FROM dbo.p21_view_invoice_hdr
WHERE (invoice_no IN (SELECT MAX(invoice_no) AS last_invoice
FROM dbo.p21_view_invoice_hdr ih
INNER JOIN p21_view_customer cs ON ih.customer_id = cs.customer_id
WHERE cs.delete_flag = 'N' GROUP BY ih.customer_id))
I want to add the customer_name column from the p21_view_customer table. Can anyone give me a hand with the correct syntax?
November 29, 2007 at 1:24 pm
You can do it this way:
SELECT TOP 100 PERCENT
customer_id,
invoice_no,
invoice_date,
(select top 1 t.customer_name from p21_view_customer t where t.customer_id = p21_view_invoice_hdr.customer_id) as 'customer_name'
FROM dbo.p21_view_invoice_hdr
WHERE (invoice_no IN (SELECT MAX(invoice_no) AS last_invoice
FROM dbo.p21_view_invoice_hdr ih
INNER JOIN p21_view_customer cs ON ih.customer_id = cs.customer_id
WHERE cs.delete_flag = 'N' GROUP BY ih.customer_id))
or do a join.
November 29, 2007 at 1:26 pm
Perfect! Thanks for your help!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply