Joins
Explanation
Like
Conditions, joining tables in a query is done by adding one root join to the query, then linking one onto that, and the next onto the second, and so on. In other words, a query does not have a list of joins, but a single join, and each join may have one join. Joins also have one condition. That doesn't mean that only one condition is allowed per join, but that an SQJoin object has one SQConditionBase property. So, any number of conditions can be applied to the join, by way of condition chaining and nesting.
We'll use this schema for the example:
Artist Column | Type | Note |
ID | int | primary key |
Name | string | |
Album Column | Type | Note |
ID | int | primary key |
ArtistID | int | Foreign key: Artist.ID |
Name | string | |
Year | int | |
Song Column | Type | Note |
AlbumID | int | Foreign key: Album.ID |
Name | string | |
Track | int | |
In this example, we'll select the artist, album, and song name for all songs.
SQSelectQuery select = new SQSelectQuery()
{
PrimaryTable = new SQAliasableObject("Song"),
Join = new SQJoin()
{
JoinObject = new SQAliasableObject("Album"),
JoinType = JoinTypes.Left,
Condition = new SQCondition("Song.AlbumID", RelationOperators.Equal, "Album.ID"),
NextJoin = new SQJoin()
{
JoinObject = new SQAliasableObject("Artist"),
JoinType = JoinTypes.Left,
Condition = new SQCondition("Album.ArtistID", RelationOperators.Equal, "Artist.ID")
}
}
};
select.Columns.AddRange(new SQAliasableObject[]
{
new SQAliasableObject("Artist.Name"),
new SQAliasableObject("Album.Name"),
new SQAliasableObject("Song.Name")
});
Set the object being joined, the type of join (left, right, or inner), the join predicate, and if needed, trail one join off of another by setting the next join.
Here's another example. This is from the GetTable method in the SeaQuail_MySQL.MySQLAdapter class. GetTable is passed the name of a table, and returns an SQTable object and its columns fully populated.
public override SQTable GetTable(string name)
{
string varTable = CreateVariable("Table");
string varPK = CreateVariable("PK");
SQSelectQuery q = new SQSelectQuery();
q.Columns.AddRange(new List<SQAliasableObject>
{
new SQAliasableObject("cols.COLUMN_NAME"),
new SQAliasableObject("IS_NULLABLE"),
new SQAliasableObject("DATA_TYPE"),
new SQAliasableObject("CHARACTER_MAXIMUM_LENGTH"),
new SQAliasableObject("NUMERIC_PRECISION"),
new SQAliasableObject("NUMERIC_SCALE"),
new SQAliasableObject("EXTRA", "IS_IDENTITY"),
new SQAliasableObject("CONSTRAINT_TYPE")
});
q.PrimaryTable = new SQAliasableObject("INFORMATION_SCHEMA.COLUMNS", "cols");
q.Join = new SQJoin()
{
JoinType = JoinTypes.Left,
JoinObject = new SQAliasableObject("INFORMATION_SCHEMA.KEY_COLUMN_USAGE", "tuse"),
Condition = new SQCondition("tuse.COLUMN_NAME", RelationOperators.Equal, "cols.COLUMN_NAME")
.And("tuse.TABLE_NAME", RelationOperators.Equal, "cols.TABLE_NAME"),
NextJoin = new SQJoin()
{
JoinType = JoinTypes.Left,
JoinObject = new SQAliasableObject("INFORMATION_SCHEMA.TABLE_CONSTRAINTS", "tcons"),
Condition = new SQCondition("tcons.CONSTRAINT_NAME", RelationOperators.Equal, "tuse.CONSTRAINT_NAME")
.And("tcons.TABLE_NAME", RelationOperators.Equal, "cols.TABLE_NAME")
.And("tcons.CONSTRAINT_TYPE", RelationOperators.Equal, varPK)
}
};
q.Condition = new SQCondition("cols.TABLE_NAME", RelationOperators.Equal, varTable);
q.Parameters.Add(new SQParameter(varTable, name));
q.Parameters.Add(new SQParameter(varPK, "PRIMARY KEY"));
...
The resulting SQL is below.
SELECT
cols.COLUMN_NAME
,IS_NULLABLE
,DATA_TYPE
,CHARACTER_MAXIMUM_LENGTH
,NUMERIC_PRECISION
,NUMERIC_SCALE
,COLUMNPROPERTY(OBJECT_ID(@Table), cols.COLUMN_NAME, 'IsIdentity') [IS_IDENTITY]
,CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.COLUMNS cols
Left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS tuse ON
(tuse.COLUMN_NAME = cols.COLUMN_NAME) AND (tuse.TABLE_NAME = cols.TABLE_NAME)
Left JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tcons ON
(tcons.CONSTRAINT_NAME = tuse.CONSTRAINT_NAME) AND (tcons.TABLE_NAME = cols.TABLE_NAME)
WHERE (cols.TABLE_NAME = @Table)