This project is read-only.

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)

Last edited Aug 5, 2010 at 4:45 AM by sledgebox, version 4

Comments

No comments yet.