Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Location
    Toronto, Canada
    Posts
    76

    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
    from invoices
    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.

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    Re: Help needed writing a query

    Originally posted by unicef2k

    I need to write a query that returns an invoiceId, billDate for every invoiceDescription.
    SELECT invoiceId, billDate
    FROM invoices i
    WHERE EXISTS (
    SELECT invoiseDiscriptionId
    FROM InvoiceDiscription Id
    WHERE id.invoiceDiscriptionID = i.invoiceDiscriptionID
    );
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •