Inner Join with Subsonic

inner-joinsubsonic

I am trying to display all records that match the last name entered into a textbox.
This requires an INNER JOIN on the "volID" column because there are 2 tables.

<asp:TextBox ID="lName" runat="server"></asp:TextBox>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" Visible="true"></asp:GridView>
<asp:linkButton ID="btnSubmit" runat="server" onclick="btnSubmit_Click" />

Code behind:

 protected void btnSubmit_Click(object sender, EventArgs e)
    {
            GridView1.DataSource = new Select("*")
            .From(PastAwardName.Schema)
            .InnerJoin(PastAwardName.VolIDColumn, PastAwardType.VolIDColumn)
            .Where(PastAwardName.Columns.LName).IsEqualTo(this.lName.Text)
            .ExecuteReader();

            GridView1.DataBind();
    }

I tried to do this from and example on Subsonics site but cannot get it working. Geeting the error below.

Server Error in '/' Application. 
________________________________________
The objects "dbo.pastAwardNames" and "dbo.pastAwardNames" in the FROM clause have the same exposed names. Use correlation names to distinguish them. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: The objects "dbo.pastAwardNames" and "dbo.pastAwardNames" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Source Error: 

Line 30: 
Line 31: 
Line 32:         GridView1.DataSource = new Select("*")
Line 33:             .From(PastAwardName.Schema)
Line 34:             .InnerJoin(PastAwardName.VolIDColumn, PastAwardType.VolIDColumn)

Best Answer

I had a similar problem to this, and discovered this page by searching on the error text "in the FROM clause have the same exposed names" and SubSonic 2.2.

Anyway, thought I would post my code, which works perfectly in Subsonic 2.2 and looks quite clean..

Hope somebody finds it useful as it had me scratching my head for a while!!

IDataReader reader = new SubSonic.Select(CityMapping.MasterCityColumn, CityMapping.ChildCityColumn, CityMappingTourType.TourTypeCodeColumn)
                .From<CityMapping>()
                .InnerJoin(CityMappingTourType.CityMappingIDColumn, CityMapping.IdColumn)
                .OrderAsc(CityMapping.Columns.MasterCity, CityMapping.Columns.ChildCity, CityMappingTourType.Columns.TourTypeCode)
                .ExecuteReader();

Related Solutions

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

Sql – Difference between JOIN and INNER JOIN

They are functionally equivalent, but INNER JOIN can be a bit clearer to read, especially if the query has other join types (i.e. LEFT or RIGHT or CROSS) included in it.

Best Answer

Related Solutions

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Sql – Difference between JOIN and INNER JOIN

Related Topic