Retrieving data from joined tables

I have 3 tables, X, Y and Z w开发者_开发技巧here X has references to the ID in Y and Z; I have the following query to output all rows in X where the Y ID and Z ID both exist:

SELECT * FROM X INNER JOIN Y ON X.Y_ID = Y.ID INNER JOIN Z ON X.Z_ID = Z.ID

Unfortunately selecting, for example, X.ID AS ID etc etc is not an option as there are too many columns.

I run this query perfectly fine, however I am using dt.Rows[i]["X.ID"], where dt is the DataTable representation of the above query, to try to retrieve the ID in table X but it is throwing an exception (Column 'X.ID' does not belong to table DefaultView.).

I have tried searching for the correct syntax to go in the column name string ("X.ID") but have been unable to find anything. Please can someone shed some light on this? Many thanks in advance.

Regards, Richard

If I understand your question correctly, you have a DataTable that holds the results of your query:

SELECT * FROM X INNER JOIN Y ON X.Y_ID = Y.ID INNER JOIN Z ON X.Z_ID = Z.ID

Then you want to select a row from the DataTable based on the ID column in table X.

You can refer to the ID column by its ordinal (its numerical position in the columns collection). For example, if the ID column is the first column in the result set from your SELECT statement, you can do:

Convert.ToInt16(dt.Rows[i][0]); // Assuming ID is an int column

EDIT Was re-reading your question, and you said "X.ID AS ID etc etc is not an option as there are too many columns". So if that is your firm position, you can ignore the last half of my answer below.

Alternatively, as jdmonty said, you can explictly select the columns. I would add that if there are column names that are common across the tables you should also alias the column names, like this:

SELECT x.ID AS ID, x.NextColumn AS NextColumn
FROM   x 
INNER JOIN Y y ON x.Y_ID = y.ID
INNER JOIN Z z ON x.Z_ID = z.ID

You could even select columns from the joined tables using the alias dot column (y.ID for example) notation.

Then you could reference the column by name:

Convert.ToInt16(dt.Rows[i]["ID"]); // Assuming ID is an int column

Explicitly selecting the columns in the query should fix this error (not to mention, an overall good practice)

 SELECT X.ID, X.NAME, X.[COLUMN], x.[ETC] FROM X INNER JOIN Y ON X.Y_ID = Y.ID INNER JOIN Z ON X.Z_ID = Z.ID

Then you may need to just put ID as the column name query to the Data Table but I can't quite recall

I think what you want is to select all columns in x, but exclude the columns from y, and z.

SELECT X.* FROM X INNER JOIN Y ON X.Y_ID = Y.ID INNER JOIN Z ON X.Z_ID = Z.ID

And then in the datatable you refer to the column by its name. The datatable has no knowledge of the X alias used on the database side.

dt.Rows[i]["ID"]

Rows[i]["ID"] should be fine as long as you selecting only from a single table which has ID column