Creating an inner join: Exercise

Explore different syntax for an inner join.

In previous activities and exercises you worked with an inner join using the JOIN keyword. In this activity you will explore a different syntax for an inner join.

Replace the word JOIN in the join Query statement with a comma (,) and then eliminate extra spaces. The Query statement should now look like this:

-- Create the join

INSERT INTO TradeResults
SELECT TradeInquiries.Symbol, 
	TradesWindow.Price,
	GETTIMESTAMP(TradesWindow)
FROM TradeInquiries, TradesWindow
WHERE TradeInquiries.Symbol = TradesWindow.Symbol;

Outside of Sybase CEP Studio, navigate to SybaseC8Repository/version/examples/GettingStarted/DataFiles (where version is the version number of Sybase CEP Engine) and then copy the file inquiries.csv to the directory data in the StockFilter project directory.

Add the following text to the end of the contents in the Queries tab (or copy the text from CombiningSourcesCCL.txt between the lines "-- Combining Sources Code Block 3" and "-- Combining Sources Code Block 4"):

-- Create an input adapter, which provides
-- data to the TradeInquiries stream

ATTACH INPUT ADAPTER TradeInquiriesReader 
TYPE ReadFromCsvFileAdapterType
TO STREAM TradeInquiries
PROPERTIES 
	FILENAME = "$ProjectFolder/data/inquiries.csv",
	LOOPCOUNT = "5",
	RATE = ".5",
	USECURRENTTIMESTAMP = "YES",
	TITLEROW = "YES",
	TIMESTAMPCOLUMN = "NO"

This statement attaches an input adapter to the TradeInquiries stream and sets some of the adapter's properties.

Run the project and then examine the output in TradeResults:

Notice that the output is similar to the output you saw in the last exercise. Because of the Attach Adapter statement, multiple rows with different values in the Symbol column arrive on TradeInquiries, which in turn produces more output rows.
Examine the Query statement in the Queries tab again:
- This statement uses a comma instead of the word JOIN to create the inner join but produces the same results.
- A comma-separated join is always an inner join and always uses a WHERE clause to create the join condition instead of an ON clause.
Stop the project.

Exercise

Complete the following exercises to reinforce the join syntax. See Solutions for a possible solution to this exercise.

Rewrite the following query to use comma-separated join syntax:

INSERT INTO FrequentWinners
SELECT PlayerScore.ID, PlayerScore.WinningNumber
FROM PlayerScore JOIN DailyRecord
ON PlayerScore.ID = DailyRecord.ID 
	AND PlayerScore.Win = TRUE 
	AND DailyRecord.DailyWins > 3;