SQL query only pulling data from one table

Question

So basically, i have 2 tables, Tenants and Owners. My stored procedure is inner joining to display the select results in textboxes. But unfortunately, the query is only pulling from the owner table and not both. I get an error when trying to display from the tenant table that the column does not exist.

@ID varchar(4)

SELECT        owner_table.ownerID AS ownID, tenant_table.tenantID, owner_table.apt_num AS aptNum, owner_table.first_name AS own_first, owner_table.last_name AS own_last, 
                     owner_table.address AS own_address, owner_table.city AS own_city, owner_table.state AS own_state, owner_table.zip AS own_zip, 
                     owner_table.phone AS own_phone, owner_table.phone_2 AS own_phone2, owner_table.notes AS own_notes, owner_table.last_update AS own_lastUpdate, 
                     tenant_table.ownerID AS ten_ownID, tenant_table.last_name, tenant_table.keyID, tenant_table.storage, tenant_table.phone, tenant_table.phone_2, tenant_table.notes,
                      tenant_table.complaints, tenant_table.lease_end, tenant_table.last_update, tenant_table.apt_status
FROM            owner_table INNER JOIN
                     tenant_table ON owner_table.ownerID = @ID AND tenant_table.tenantID = @ID
RETURN

Code trying to get a result from the procedure.

private void LoadInfo(string ID)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {                           

            SqlCommand Command = new SqlCommand();
            Command.Connection = connection;
            Command.CommandText = "MoreInfoData";
            Command.CommandType = CommandType.StoredProcedure;
            Command.Parameters.Add(new SqlParameter("@ID", SqlDbType.VarChar, 4)).Value = ID;

            table = new DataTable();
            SqlDataAdapter MyAdapter = new SqlDataAdapter();
            MyAdapter.SelectCommand = Command;
            MyAdapter.Fill(table);

            if (table.Rows.Count > 0)
            {
                ten_name.Text = table.Rows[0]["last_name"].ToString();                       
            }

    }

Impossible: you are either getting results from both table or no results at all. It's not that "the query is only pulling from the owner table", but most likely your data access code is using the wrong column names for the tenant columns. Can you show the code that errors; your query is probably fine. — G. Stoynev, Jun 14 '13 at 00:46
Error im getting http://i.imgur.com/hwhbKIg.png Also, both tables have 206 records in it. When looking at the dataset rows, it has a total of only 206. Showing it only pulled in one table — Rob Shnayder, Jun 14 '13 at 00:52
You can check the actual columns name if you check the datatable in the debugger: http://stackoverflow.com/questions/1337084/how-to-view-a-datatable-while-debugging — fcuesta, Jun 14 '13 at 01:26
`JOIN` combines rows *horizontally*, adding *columns*. If you join 206 rows from one table with 206 rows from another table, where there is a 1-to-1 match, then you will **only get 206 rows**. Am I missing something here? — ErikE, Jun 14 '13 at 01:26

score 1 · Answer 1 · answered Jun 14 '13 at 00:46

1

Modify to use the column alias:

ten_name.Text = table.Rows[0]["own_last"].ToString();

answered Jun 14 '13 at 00:46

cha

10,301
1
18
26

score 1 · Answer 2 · answered Jun 14 '13 at 00:56

1

You changed your field name as own_last, so of course it doesnt exist. owner_table.last_name AS own_last. So you should change your code into

ten_name.Text = table.Rows[0]["own_last"].ToString();

answered Jun 14 '13 at 00:56

kayla

156
1
12

could you post your `DataTable()` please? – kayla Jun 14 '13 at 03:47

score 0 · Answer 3 · answered Jun 14 '13 at 00:57

0

Have you tried adding an alias for tenant_table.last_name?:

SELECT .... tenant_table.last_name AS tenant_last_name

and then:

ten_name.Text = table.Rows[0]["tenant_last_name"].ToString();

answered Jun 14 '13 at 00:57

fcuesta

4,429
1
18
13

score 0 · Answer 4 · answered Jun 14 '13 at 11:08

Can you run this and see what comes in the output, should list all your column names for the datatable you are retrieving.

if (table.Rows.Count > 0)
{
    foreach (System.Data.DataColumn col in table.Columns)
    {
        System.Diagnostics.Debug.WriteLine(col.ColumnName);
    }
}

score 0 · Answer 5 · answered Jun 18 '13 at 20:51

0

All in all, somehow whatever i saved to my query at the time wouldnt save for some reason. This ended up being the problem after all. Thanks for all the help.

answered Jun 18 '13 at 20:51

Rob Shnayder

51
6

SQL query only pulling data from one table

5 Answers5