database concepts

Advanced SQL : advanced querying

Using the same table more than once in a query

In the first topic, we saw how to retrieve data from two or more tables by joining them together. This same technique can be applied to a single table if a query requires data from different rows in a single table be compared. The table is simply joined to itself.

For example, using the INVOICE database, what customer or customers have invoices on the 16-Sep-2004 and 6-Oct-2004? If the query involved just one date, it would be quite simple, but as we want two different dates, it becomes more interesting.

In this example we create two instances of the table INVOICE and compare them against each other (remember that SQL can only compare the contents of one row at a time). Any records that meet the criteria are then displayed.

Putting this all together to answer the query:

     Find the customers that have invoices dated 16-Sep-2004 and 6-Oct-2004

     SELECT i1.CustABN
     FROM INVOICE i1, INVOICE i2
     WHERE i1.CustABN = i2.CustABN
     AND TRUNC(i1.InvDate) = DATE '2004-9-16'
     AND TRUNC(i2.InvDate) = DATE '2004-10-6';

Note the use of the aliases i1 and i2 to distinguish between the two instances of the INVOICE table. All attributes (column names) need to be fully qualified.

The query can be modified to show the customer's name:

     SELECT i1.CustABN || ' ' || c.CustName AS "Invoices 16/9 and 6/10 2004"
     FROM INVOICE i1, INVOICE i2, CUSTOMER c
     WHERE i1.CustABN = i2.CustABN
     AND TRUNC(i1.InvDate) = DATE '2004-9-16'
     AND TRUNC(i2.InvDate) = DATE '2004-10-6'
     AND i1.CustABN = c.CustABN;