Home

SQL Data Joins With ADO.NET

 

Joins

 

Introduction

When studying relationships, we reviewed techniques of making data from one table available to the records of another table. This demonstrated to reduce data duplication and mistakes. Another issue that involves the combination of tables consists of creating records from more than one table and making the result into a single list. This is the basis of data joins.

A data join is a technique of creating a list of records from more that one table, using all columns from all tables involved, or selecting only the desired columns from one or all of the tables involved. This means that a data join is essentially created in three steps:

  1. The tables that will be involved in the join
  2. A column that will create the link in each table
  3. A SQL statement that will create the records
 

The Tables of a Join

Before creating a join, you must have the tables that would be involved. The tables are created using the techniques we have seen in previous lessons. It is also important to create a primary key for each table. The parent table would usually need only this primary key that would be used to "link" it to a child table. Here is an example of such a table:

If needed, you can then create the necessary records for this type of table. Here is an example:

When creating the child table, remember to create a column that would serve as the link with the parent table. By a (good) habit as we saw when studying relationships, the name and the data type of this column are the same as the primary key of the parent table. Here is an example of a child table that would be joined to the above parent table:

Once again, if necessary, you can add the needed records to the table. Here is an example:

 
 

Practical Learning Practical Learning: Introducing Data Joins

  1. Start Microsoft SQL Server and the SQL Query Analyzer
  2. To create the database for the current exercise, execute the following statement:
     
    -- =============================================
    
    -- Database: CarInventory2
    
    -- =============================================
    
    IF EXISTS (SELECT * 
    
    	   FROM   master..sysdatabases 
    
    	   WHERE  name = N'CarInventory2')
    
    	DROP DATABASE CarInventory2
    
    GO
    
    
    
    CREATE DATABASE CarInventory2
    
    GO
  3. Save the statement as CarInventory2.sql and open a new window
  4. To create one of the tables used in this database, execute the following statement:
     
    -- =============================================
    
    -- Database: CarInventory2
    
    -- Table:    CarsCategories 
    
    -- =============================================
    
    USE CarInventory2
    
    GO
    
    IF EXISTS (SELECT name 
    
    	   FROM   sysobjects 
    
    	   WHERE  name = N'CarsCategories' 
    
    	   AND 	  type = 'U')
    
        DROP TABLE CarsCategories
    
    GO
    
    
    
    create table CarsCategories (
    
    CarCategoryID int IDENTITY(1, 1) PRIMARY KEY NOT NULL, 
    
    CarCategory varchar(50) NOT NULL,
    
    DailyRate varchar(10),
    
    WeeklyRate varchar(10),
    
    MonthlyRate varchar(10),
    
    WeekendRate varchar(10))
    
    GO
    
    
    
    Insert Into CarsCategories(CarCategory, DailyRate, WeeklyRate, MonthlyRate, WeekendRate)
    
    Values('Economy', '32.95', '29.75', '22.95', '19.95')
    
    GO
    
    Insert Into CarsCategories(CarCategory, DailyRate, WeeklyRate, MonthlyRate, WeekendRate)
    
    Values('Compact', '39.95', '34.75', '24.95', '29.95')
    
    GO
    
    Insert Into CarsCategories(CarCategory, DailyRate, WeeklyRate, MonthlyRate, WeekendRate)
    
    Values('Standard', '45.95', '39.75', '35.95', '34.95')
    
    GO
    
    Insert Into CarsCategories(CarCategory, DailyRate, WeeklyRate, MonthlyRate, WeekendRate)
    
    Values('Full Size', '49.95', '42.75', '35.95', '38.95')
    
    GO
    
    Insert Into CarsCategories(CarCategory, DailyRate, WeeklyRate, MonthlyRate, WeekendRate)
    
    Values('Mini Van', '55.95', '50.95', '45.95', '42.95')
    
    GO
    
    Insert Into CarsCategories(CarCategory, DailyRate, WeeklyRate, MonthlyRate, WeekendRate)
    
    Values('SUV', '55.95', '50.95', '45.95', '42.95')
    
    GO
    
    Insert Into CarsCategories(CarCategory, DailyRate, WeeklyRate, MonthlyRate, WeekendRate)
    
    Values('Truck', '42.95', '35.75', '30.95', '30.95')
    
    GO
    
    Insert Into CarsCategories(CarCategory, DailyRate, WeeklyRate, MonthlyRate, WeekendRate)
    
    Values('Van', '69.95', '59.75', '50.75', '49.95')
    
    GO
  5. Save the statement as CarsCategories.sql and open a new window
  6. To create another table used in this database, execute the following statement:
     
    -- =============================================
    
    -- Database: CarInventory2
    
    -- Table:    Cars
    
    -- =============================================
    
    USE CarInventory2
    
    GO
    
    IF EXISTS (SELECT name 
    
    	   FROM   sysobjects 
    
    	   WHERE  name = N'Cars' 
    
    	   AND    type = 'U')
    
        DROP TABLE Cars
    
    GO
    
    
    
    CREATE TABLE Cars (
    
    CarID int PRIMARY KEY Identity(1, 1) NOT NULL,
    
    TagNumber varchar(10),
    
    Make varchar(50),
    
    Model varchar(50),
    
    CarYear varchar(5),
    
    CategoryID int,
    
    HasK7Player bit DEFAULT(0),
    
    HasCDPlayer bit DEFAULT(0),
    
    HasDVDPlayer bit DEFAULT(0),
    
    Available bit DEFAULT(1))
    
    GO
    
    
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('HAD-722', 'Hyundai', 'Accent', '2003', 1, 0, 0, 1, 1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('CDJ-85F', 'Mercury', 'GrandMarquis', 1998,	4,	0,	0,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('FGE-920', 'Ford', 'Escape',	2004, 6,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('GMM-186', 'Mercury', 'Grand Marquis', 2001,	4,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('GHL-22G', 'Lincoln', 'TownCar', 1998,	4,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('HHS-382', 'Hyundai', 'Sonata', 2002,	2,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('LBN-755', 'Lincoln', 'Navigator', 2000,	6,	1,	1,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('FDX-984', 'Kia', 'Sephia',	2002,	2,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('AFS-888', 'Ford', 'SportTrac', 1998,	7,	0,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('CAM-422', 'Chevrolet', 'Metro',	2000,	1,	0,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('QFH-608', 'Ford', 'F150', 2001,	7,	0,	0,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DCC-713', 'Chevrolet', 'Camaro',	2001,	3,	1,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('LFT-268', 'Ford', 'Club Wagon',	1998,	5,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('PBR-69G', 'Buick', 'Regal',	2000, 4,	0,	1,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DBP-832', 'Buick', 'Park Avenue',	2001,	4,	0,	0,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('FM-685', 'Ford', 'Mustang Convertible',	2002,	3,	1,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('FAX-48T', 'Mecury', 'Villager',	1999,	5,	1,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DPM-42', 'Pontiac',	'Mountana',	2002,	5,	0,	1,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('AFW-928', 'Ford', 'Windstar Minivan GL',	2001,	5,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('UFX-963', 'Cadillac', 'Sedan de Ville',	1998,	4,	1,	1,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('KCR-656', 'Chevrolet', 'Blazer',	2001,	6,	0,	0,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('LLT-358', 'Lincoln', 'City Car',	2004,	4,	0,	1,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('RBL-618', 'Buick', 'LeSabre',	2002,	4,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('WFV-688', 'Ford', 'E350',	2000,	8,	0,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('GCV-557', 'Chevrolet', 'Camaro',	1999,	3,	0,	1,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('TPG-905', 'Pontiac', 'Grand Am',	2002,	2,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('JYW-682', 'Jeep', 'Wrangler',	2003,	6,	1,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DFR-214', 'Ford', 'Ranger',	2000,	7,	0,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('RKR-670', 'Kia', 'Rio',	2002,	1,	1,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('YJC-498', 'Ford', 'Escort',	1996,	1,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('QDC-922', 'Dodge', 'Caravan',	2002,	5,	1,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DSS-374', 'Hyundai', 'Accent',	1996,	1,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('SCC-262', 'Chrysler', 'Concorde',	2002,	4,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('BDI-588', 'Dodge', 'Intrepid',	2004,	2,	1,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('MCM-952', 'Jaguar', 'S-Type',	1999,	4,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('HDN-167', 'Dodge', 'Neon',	2002,	1,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('EDS-664', 'Ford', 'Explorer XLT',	1998,	6,	1,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('C362344', 'Dodge', 'Caravan', 2002,	5,	1,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('753723', 'Honda', 'Civic', 1999,	2,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('846884', 'Chrysler', 'Concorde', 2002,	4,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DK-3622', 'Dodge', 'Intrepid', 2004,	2,	1,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('3826DD', 'Chrysler', '300M',	2004, 3,	0,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('683678', 'Nissan', 'Sentra',	1996,	1,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('PWEY22', 'Dodge', 'Stratus',	2003,	3,	1,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('D276332', 'Hyundai', 'Elantra', 1996,	2,	1,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('CDJ-855', 'Jeep', 'Cherokee', 2002,	6,	1,	0,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('273222G', 'Ford', 'Minivan GL', 2001,	5,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DF4-888', 'Mazda', 'Protégé', 1997,	1,	0,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('ACV-557', 'Chevrolet', 'Cavalier',	2003,	2,	0,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('PPL-498', 'Jeep',	'Grand Cherokee Laredo',	2001,	6,	0,	1,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('268952', 'Chrysler',	'300M',	2004,	3,	0,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('F6348T', 'Ford',	'Explorer XLT',	2000,	6,	1,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('C367264', 'Ford',	'Taurus',	2002,	4,	1,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('KKL-348', 'Toyota', 'Corolla',	1998,	2,	0,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('827835V', 'Dodge', 'Neon',	2002,	1,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('KWL-888', 'Ford', 'Focus',	2002,	1,	0,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('382963', 'Ford', 'Contour',	1999,	2,	1,	1,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('SWW-664', 'Dodge', 'Stratus',	2003,	3,	1,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DLO-723', 'Mercury', 'Mystique',	1998,	3,	1,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('GTE-332', 'Daewoo', 'Lanos',	2000,	2,	1,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('D82963', 'Ford', 'Expedition XLS',	2004,	6,	1,	1,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('362-723', 'Dodge', 'Sebring',	2002,	3,	1,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DDG-984', 'Fort', 'Expedition XLT',	2000,	6,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('D287323', 'Dodge', 'Sebring',	2002,	3,	1,	0,	0,	0)
    
    GO
  7. Save the statement as Cars.sql
 

Join Creation

Equipped with the necessary tables and their columns, you can create the join. To do this in the SQL Server Enterprise Manager, you can right-click one of the tables involved, position the mouse on Open Table, and click Query. This would display the Data In Table window with the table in the Diagram section. Because the foundation of a join lies on at least two tables, you should add one. To do this, you can click the Add Table button on the toolbar or you can right-click any section of the window and click Add table... This would display the Add Table button dialog box. To select a table, you can click it and click Add. After selecting the table(s), you can click Close. Here is an example:

If a relationship was already established between the tables, a joining line would show it.

In SQL code, the basic formula to create a join is:

SELECT WhatColumn(s)

FROM ChildTable

TypeOfJoin ParentTable

ON Condition

The ChildTable factor specifies the table that holds the records that will be retrieved. It cab be represented as follows:

SELECT WhatColumn(s)

FROM Persons

TypeOfJoin ParentTable

ON Condition

The ParentTable factor specifies the table that holds the column with the primary key that will control what records, related to the child table, that will display. This factor would be represented as follows:

SELECT WhatColumn(s)

FROM Persons

TypeOfJoin Genders

ON Persons.GenderID = Genders.GenderID

The Condition factor is a logical expression used to validate the records that will be isolated. To created the condition, you should assign the primary key column of the parent table to the foreign key column of the child table. Because both columns likely have the same name, to distinguish them, their names should be qualified. This would be done as follows:

SELECT WhatColumn(s)

FROM Persons

TypeOfJoin Genders

ON Persons.GenderID = Genders.GenderID

The WhatColumn(s) factor of our formula allows you to make a list of the columns you want to include in your statement. As you should be aware, you can include all columns by using the * operator. Here is an example:

SELECT *

FROM Persons

TypeOfJoin Genders

ON Persons.GenderID = Genders.GenderID

In this case, all columns from all tables would be included in the result. Instead of all columns, you may want a restricted list. In this case, create the list after the SELECT keyword separating them with commas. You can use the name of a column normally if that name is not duplicated in more than one column. Here is an example:

SELECT LastName, FirstName, Gender

FROM Persons

TypeOfJoin Genders

ON Persons.GenderID = Genders.GenderID

If the same name of a column is found in more than one table, as is the case for a primary-foreign key combination, you should qualify the name. Here is an example:

SELECT LastName, FirstName, Persons.GenderID,

             Genders.GenderID, Gender

FROM Persons

TypeOfJoin Genders

ON Persons.GenderID = Genders.GenderID

In fact, to make your code easier to read, you should qualify the name of each column of your SELECT statement. Here is an example:

SELECT Persons.LastName, Persons.FirstName, Persons.GenderID,

             Genders.GenderID, Genders.Gender

FROM Persons

TypeOfJoin Genders

ON Persons.GenderID = Genders.GenderID
 

Previous Copyright © 2005 FunctionX, Inc. Next