This tutorial shows in a simple Cω program how to use the JOIN clause within a SQL Select expression.
To run this tutorial, you may use the following project and source files:
These files are located in the \samples\SQL\Join subdirectory under the path where you installed Cω, which by default is C:\Program Files\Comega.
To run this sample, you need to have a SQL configuration file in the same directory as the Select.exe you make in Visual Studio. Here are the contents of the version of this file that Cω provided you as a starting point:
<configuration>
<appSettings>
<add key="Northwind" value="Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI" />
</appSettings>
</configuration>
Verify that this is the correct SQL connection string for connecting to the
Northwind database on your SQL Server 2000 installation. If necessary, you can
modify the value attribute in the <add> element
When you have verified it is correct, press F5 in Visual Studio to make and run the sample.
Joins are used to tie together SQL results yielded by the use of SQL Select expressions.
The following table lists the types of join operations that are available:
| Type of Join | Description |
|---|---|
| Inner join | Produces rows where the ON condition is True. |
| Left join | Produces an inner join plus at least one row for each left side row. |
| Right join | Produces an inner join plus at least one row for each right side row. |
| Full join | Produces an inner join plus at least one row for left and right side rows. |
The following is a complete Cω program that demonstrates how to use the JOIN operation to tie order details with products in the results of yielded from a SQL Select expression used to query the Northwind sample database.
using System;
using System.Data.SqlTypes;
using System.Query;
using Northwind;
public class Test {
static void Main() {
// Use an inner join to tie OrderDetails with Products. Only OrderDetails with
// a proper association to the Products table will be considered.
results =
select top 20 od.OrderID, od.Quantity, p.ProductName, p.UnitsInStock
from od in DB.OrderDetails inner join p in DB.Products on od.ProductID == p.ProductID
where od.Quantity > p.UnitsInStock
order by od.OrderID;
Console.WriteLine("Order items that exceed stock on hand.\n");
foreach( row in results ) {
Console.WriteLine("{0,5} {1,5} {2,5} {3}", row.OrderID, row.Quantity, row.UnitsInStock, row.ProductName);
}
Console.Write("\nPress ENTER to continue...");
Console.ReadLine();
}
}
Order items that exceed stock on hand. 10249 40 20 Manjimup Dried Apples 10250 35 20 Manjimup Dried Apples 10252 40 19 Camembert Pierrot 10253 20 0 Gorgonzola Telino 10253 40 10 Maxilaku 10254 21 4 Longlife Tofu 10255 20 17 Chang 10255 35 29 Pavlova 10256 15 0 Perth Pasties 10258 50 17 Chang 10258 65 0 Chef Anton's Gumbo Mix 10259 10 3 Sir Rodney's Scones 10260 50 36 Ravioli Angelo 10260 21 15 Outback Lager 10261 20 3 Sir Rodney's Scones 10262 12 0 Chef Anton's Gumbo Mix 10263 60 29 Pavlova 10263 28 20 Guaranį Fantįstica 10263 60 10 Nord-Ost Matjeshering 10263 36 4 Longlife Tofu Press ENTER to continue...