Thread: Help needed writing a query
12-16-03, 11:46 #1Registered User
- Join Date
- Sep 2002
- Toronto, Canada
Unanswered: Help needed writing a query
I have two tables: invoices and invoiceDescriptions
invoices has three columns: invoiceId, invoiceDescriptionId, and billDate. The invoiceDescriptionId is a foreign key to the invoiceDescriptions table. The invoiceDescriptions table has two columns: invoiceDescriptionId and name.
The relationship between the invoices and invoiceDescriptions is many-to-1. So there are many invoices for a gives invoiceDescription.
I need to re-write the following query:
select invoiceId, issuedate, invoiceDescriptionId
where (issuedate, invoiceDescriptionId) in (select max(issuedate), invoiceDescriptionId from invoices group by invoiceDescriptionId);
The above query would be used as part of a bigger query and I'm looking for alternate queries. Can someone suggest other queries that does the same thing?
Last edited by unicef2k; 12-16-03 at 12:10.
12-16-03, 12:00 #2Registered User
- Join Date
- Apr 2003
- Minneapolis, MN
Re: Help needed writing a queryOriginally posted by unicef2k
I need to write a query that returns an invoiceId, billDate for every invoiceDescription.
FROM invoices i
WHERE EXISTS (
FROM InvoiceDiscription Id
WHERE id.invoiceDiscriptionID = i.invoiceDiscriptionID
MS Computer Science
OCP DBA 9i/8i