Start of Tutorial > Start of Trail > Start of Lesson

Search Feedback Form

Using Joins

Sometimes you need to use two or more tables to get the data you want. For example, suppose the proprietor of The Coffee Break wants a list of the coffees he buys from Acme, Inc. This involves information in the COFFEES table as well as the yet-to-be-created SUPPLIERS table. This is a case where a join is needed. A join is a database operation that relates two or more tables by means of values that they share in common. In our example database, the tables COFFEES and SUPPLIERS both have the column SUP_ID , which can be used to join them.

Before we go any further, we need to create the table SUPPLIERS and populate it with values.

The code below creates the table SUPPLIERS :

String createSUPPLIERS = "create table SUPPLIERS " +  
			 "(SUP_ID INTEGER, SUP_NAME VARCHAR(40), " + 
			 "STREET VARCHAR(40), CITY VARCHAR(20), " +
			 "STATE CHAR(2), ZIP CHAR(5))";
stmt.executeUpdate(createSUPPLIERS);

The following code inserts rows for three suppliers into SUPPLIERS :

stmt.executeUpdate("insert into SUPPLIERS values (101, " +
    "'Acme, Inc.', '99 Market Street', 'Groundsville', " + "'CA', '95199'");
stmt.executeUpdate("Insert into SUPPLIERS values (49," +
    "'Superior Coffee', '1 Party Place', 'Mendocino', 'CA', " + "'95460'");
stmt.executeUpdate("Insert into SUPPLIERS values (150, " + 
    "'The High Ground', '100 Coffee Lane', 'Meadows', 'CA', " + "'93966'");

The following code selects the whole table and lets us see what the table SUPPLIERS looks like:

ResultSet rs = stmt.executeQuery("select * from SUPPLIERS");

The result set will look similar to this:

SUP_ID	SUP_NAME	STREET	CITY	STATE	ZIP
------	-------------	-------------	-----------	-----	-----
101	Acme, Inc.	99 Market Street	Groundsville	CA	95199
49	Superior Coffee	1 Party Place	Mendocino	CA	95460
150	The High Ground	100 Coffee Lane	Meadows	CA	93966

Now that we have the tables COFFEES and SUPPLIERS , we can proceed with the scenario where the owner wants to get a list of the coffees he buys from a particular supplier. The names of the suppliers are in the table SUPPLIERS , and the names of the coffees are in the table COFFEES . Since both tables have the column SUP_ID , this column can be used in a join. It follows that you need some way to distinguish which SUP_ID column you are referring to. This is done by preceding the column name with the table name, as in " COFFEES.SUP_ID " to indicate that you mean the column SUP_ID in the table COFFEES . The following code, in which stmt is a Statement object, will select the coffees bought from Acme, Inc.:

String query = "
SELECT COFFEES.COF_NAME " +
   "FROM COFFEES, SUPPLIERS " +
   "WHERE SUPPLIERS.SUP_NAME LIKE 'Acme, Inc.' " +
   "and SUPPLIERS.SUP_ID = COFFEES.SUP_ID";

ResultSet rs = stmt.executeQuery(query);
System.out.println("Coffees bought from Acme, Inc.: ");
while (rs.next()) {
    String coffeeName = rs.getString("COF_NAME");
    System.out.println("     " + coffeeName);
}

This will produce the following output:

Coffees bought from Acme, Inc.:
     Colombian
     Colombian_Decaf

Start of Tutorial > Start of Trail > Start of Lesson

Search Feedback Form