Problem with JOIN

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RuffAroundTheEdges - 06 Jul 2005 09:38 GMT

I'm learning aspx. I have the following code in my aspx:

SqlConnection connection = new
SqlConnection("server=(local)\\NetSDK;database=pubs;Integrated
Security=SSPI");

String sqlCommand = "SELECT * FROM Titles INNER JOIN MyTable ON
MyTable.title_id=Titles.title_id";

SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
.
.
.
This code returns the correct data set. However, when I use this code:

SqlConnection connection = new
SqlConnection("server=(local)\\NetSDK;database=pubs;Integrated
Security=SSPI");

String sqlCommand = "SELECT Titles.title_id, title, type, pub_id, price,
notes, pubdate FROM Titles INNER JOIN MyTable ON
MyTable.title_id=Titles.title_id";

SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
.
.
.

the returned data set does not contain the MyTable data.
What is Wrong?

Reply to this Message

Christian Donner - 06 Jul 2005 10:29 GMT

"RuffAroundTheEdges" schrieb:

> I'm learning aspx. I have the following code in my aspx:
>
[quoted text clipped - 21 lines]
> the returned data set does not contain the MyTable data.
> What is Wrong?

You are not asking for data from MyTable. All the fields in the select list
are fields from 'titles', aren't they?

Reply to this Message

Jens Süßmeyer - 06 Jul 2005 10:37 GMT

If you place the star (*) in the selection list without any prefix for any
table, even all joined tables are used for selecting the columns, If you are
specifying the columns only these are selected.

So in your second example you should go for that statement if you want to
get those columns in addition:

> String sqlCommand = "SELECT Titles.title_id, title, type, pub_id, price,
> notes, pubdate,

myTable.*
FROM Titles INNER JOIN MyTable ON

> MyTable.title_id=Titles.title_id";

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

> I'm learning aspx. I have the following code in my aspx:
>
[quoted text clipped - 26 lines]
> the returned data set does not contain the MyTable data.
> What is Wrong?

Reply to this Message

RuffAroundTheEdges - 07 Jul 2005 01:04 GMT

The example shown in the FROM document page at;
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_pubs
_2v8l.asp

has:

SELECT ProductID, Suppliers.SupplierID
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)

Reply to this Message

RuffAroundTheEdges - 07 Jul 2005 02:34 GMT

I had to do more reading and found that the SELECT statement works
differently in a JOIN:

SELECT Titles.title.id, ..., MyTable.stuff FROM Titles JOIN MyTable ON
(MyTable.title_id = Titles.title_id)

Reply to this Message