|
|
Start of Tutorial > Start of Trail > Start of Lesson |
Search
Feedback Form |
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 theCOFFEEStable as well as the yet-to-be-createdSUPPLIERStable. 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 tablesCOFFEESandSUPPLIERSboth have the columnSUP_ID, which can be used to join them.Before we go any further, we need to create the table
SUPPLIERSand 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
SUPPLIERSlooks like:ResultSet rs = stmt.executeQuery("select * from SUPPLIERS");The result set will look similar to this:
SUP_ID SUP_NAME ------ 101 49 150 Now that we have the tables
COFFEESandSUPPLIERS, 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 tableSUPPLIERS, and the names of the coffees are in the tableCOFFEES. Since both tables have the columnSUP_ID, this column can be used in a join. It follows that you need some way to distinguish whichSUP_IDcolumn 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 columnSUP_IDin the tableCOFFEES. The following code, in whichstmtis aStatementobject, 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 |
Copyright 1995-2004 Sun Microsystems, Inc. All rights reserved.