CODEWITHSUNDEEP
c#sqlms-access
Val
Val

Reputation: 1832

SQL: Joining tables on primary key AND returning the primary key

I'm writing a C# application and am using an Access .mdb. I have a table with email messages and a table with message relations (each email msg can be assigned to several teams of workers), so the rows in the relations table have "msgId" and "teamName" fields.

I want to to get all messages from the first table which are assigned to a specified team. I'm using the following query:

"SELECT * FROM Mails INNER JOIN MailAssignments ON Mails.msgId = MailAssignments.msgId"

But it doesn't return the msgId for me, I guess, because the tables are joined on this field, but then I m not able to identify messages in my C# code.

How can I make the query return the msgId for me?

Upvotes: 0

Views: 98

Answers (2)

onedaywhen
onedaywhen

Reputation: 57023

It should appear twice in the resultset, for Mails.msgId and MailAssignments.msgId respectively. However, you should not expect two columns named msgId. Rather, the DBMS should disambiguate the columns. This is a requirement of Standards SQL, BTW.

IIRC Access will rename both columns in order to disambiguate them. This would explain why there is no column named msgId in the result.

Upvotes: 0

aleroot
aleroot

Reputation: 72636

It is enough specify the fields name in the selection, or add the table name where you want to get all the fields, try with this selection list : 

SELECT Mails.* 
FROM Mails INNER JOIN MailAssignments 
ON Mails.msgId = MailAssignments.msgId

Upvotes: 2

Related Questions