Some Advanced SQL Scripts and Tips
The power of SQL scripts comes from the ability to connect multiple tables together and produce one or more reports. A join is an SQL method used to get information from more than one table, depending on the relationship between them. The primary keys are the links between the tables.
Advanced Select Query
Select Period_ID, Period_Desc, Market_ID, Units, Dollars, Brand
from Period, Sales, Market, Product
where Period.Period_Id = Sales.Period_id, Sales.Product_Id = Product.Product_Id, Sales.Period_ID=Market.Market_Id;
Here is a breakdown of this code. The programmer wants to get information from all four tables. Each table is connected to another by way of the keys: Period_Id (Period and Sales), Market_Id(Sales and Market), and Product_Id(Sales and Product). The information that is needed is from five columns: Period_ID, Period_Desc, Market_ID, Units, Dollars, Brand.
Inner Join Query
An inner join returns rows when there is at least one match in both tables. This is the structure of an inner join:
FROM table_1 JOIN table_2
ON table_1. primarykey = table_2.foreignkey
Select Period.Period_Id, Sales.Period_Id
from Period Join Sales
where Period.Period_Id = Sales.Period_Id
Left Outer Join
This query returns all of the rows from the left table, even if in the right table there are no matches.
Here is some sample code for a left outer join:
Select Sales.Period_ID, Market.Market_ID
left outer join Market
On Sales.Market_ID = MarketMarket._ID
Right Outer Join
The logic behind this is that the query returns all of the rows from the right table, even if in the left table there are no matches.
Select Sales.Period_ID, Product.Product_ID
Right outer join Sales
On Sales.Market_ID = Product.Product_ID
From these examples, you can see that creating an image can help you anticipate the results that you are looking for. In this case the Venn diagram can help identify where the results are expected from. For more on Venn Diagrams see Math Lesson Plans in Venn Diagrams.