Table join question

[Guest]

I'm using ASP.NET and SQL Server and this might be an obviuos question for
most, but if I have a table that contains several fields that I need to
relate to just one field in another table, how do I do that?

I.e. Table 1 has Integer values for enteredbyID, CoordID, CustContactID
Table 2 is the contacts table with Contact_ID (integer) key. Table 1 uses
that field to bring back First and Last Name's.

So:
Table1 Table 2
enteredbyID Contact_ID
CoordID Contact_ID
CustContactID Contact_ID

I'm fine if I link one field in Table 1 to Table 2.

I can't make join from All Table 1 fields to Contact_ID field??

thanx.

 

[Kevin Spencer]

Hi Chris,

Here's the thing: A table (result set) has the same number of rows as
records, and the same number of columns per record as the number of columns
fetched in the query. So, you're not going to be able to eliminate the
duplicate ContactIDs in your result set. HOWEVER, that doesn't mean you have
to DISPLAY them all.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.

 

[pj]

I'm using ASP.NET and SQL Server and this might be an obviuos question for
most, but if I have a table that contains several fields that I need to
relate to just one field in another table, how do I do that?

I.e. Table 1 has Integer values for enteredbyID, CoordID, CustContactID
Table 2 is the contacts table with Contact_ID (integer) key. Table 1 uses
that field to bring back First and Last Name's.

So:
Table1 Table 2
enteredbyID Contact_ID
CoordID Contact_ID
CustContactID Contact_ID

I'm fine if I link one field in Table 1 to Table 2.

I can't make join from All Table 1 fields to Contact_ID field??

I'm not quite sure I understand your question properly...

if you have:

table1:
enteredbyID
CoordID
CustContactID


and table2:
ContactID
FirstName
LastName

where table1.CustContactID refers to table2.ContactID

then you can use a query to join the tables e.g.

SELECT table1.*, table2.FirstName, table2.LastName FROM table1
INNER JOIN table2 ON table1.CustContactID = table2.ContactID

which will give you:

datatable:
enteredbyID
CoordID
CustContactID
FirstName
LastName

which you can bind to your datagrid.

is that what you're after or have I misunderstood?

pj

 

[Guest]

The issue is that I need to create a grid where I display the Names of the
people not the Integter ID for three fields. So the only table that I have
the Name in is the Contacts table related to those integter value. I can join
for one field (i.e EnteredBY) but the other two go wtihout. I wrote a
function to handle this on the pages, i.e. I send the integer value to gte
back the Full Name. But I'm wondering if I can do this here in the Grid. I
using (showing for one working Name):

<asp:TemplateColumn HeaderText="Assigned To"
HeaderStyle-CssClass="gridqtext" ItemStyle-CssClass="gridtext">
<ItemTemplate>
<asp:Label id="lblassignedto" width="150" runat="server" Text='<%#
DataBinder.Eval(Container.DataItem, "FullName") %>' Visible="True">
</asp:Label>
</ItemTemplate>
</asp:TemplateColumn>

I'm wondering if there is a simple way to use my function here in the grid:
qc2005s.GetName(Person_ID value)

Maybe in the OnCreate for the Grid? But how?

So, via the table themselves is out I take it.

Thanx.

 

[Guest]

you got it but I need to do all three at once. I.e. bring back the fullname
for each of Table 1's integer values. Table 1 has three fields that I need to
convert in the grid to fullname, via a view if possible (see reply to this).

thanx.

 

[pj]

The issue is that I need to create a grid where I display the Names of the
people not the Integter ID for three fields. So the only table that I have
the Name in is the Contacts table related to those integter value. I can join
for one field (i.e EnteredBY) but the other two go wtihout. I wrote a
function to handle this on the pages, i.e. I send the integer value to gte
back the Full Name. But I'm wondering if I can do this here in the Grid. I
using (showing for one working Name):

<asp:TemplateColumn HeaderText="Assigned To"
HeaderStyle-CssClass="gridqtext" ItemStyle-CssClass="gridtext">
<ItemTemplate>
<asp:Label id="lblassignedto" width="150" runat="server" Text='<%#
DataBinder.Eval(Container.DataItem, "FullName") %>' Visible="True">
</asp:Label>
</ItemTemplate>
</asp:TemplateColumn>

I'm wondering if there is a simple way to use my function here in the grid:
qc2005s.GetName(Person_ID value)

Maybe in the OnCreate for the Grid? But how?

So, via the table themselves is out I take it.

ok, I think I understand what you mean. let me try again:

table1:enteredbyID:CoordID:CustContactID
row1: 1: 2: 3

table2: ContactID: FirstName: SecondName
row1: 1 : John : Smith
row2: 2 : Susan : Scott
row3: 3: Mickey : Mouse


Your query becomes:

SELECT
EnteredBy.FirstName AS EnteredByFirstName,
EnteredBy.LastName AS EnteredByLastName,
Coord.FirstName AS CoordFirstName,
Coord.LastName AS CoordByLastName,
CustContact.FirstName AS CustContactFirstName,
CustContact.LastName AS CustContactLastName
FROM table1 AS T
INNER JOIN table2 AS EnteredBy ON EnteredBy.ContactID = T.enteredbyID
INNER JOIN table2 AS Coord ON Coord.ContactID = T.coordID
INNER JOIN table2 AS CustContact ON CustContact.ContactID =
T.CusrtcontactID

and your results set just has the names you need.

pj

 

[Kevin Spencer]

Wait a minute. I'm a little confused. I just looked at your table layout
from your first message, and I have a couple of questions:

At first I thought this was a result set with 2 columns (yeah, I know, I
should have looked closer). But as I reconsider it, it looks like 2 tables,
all the columns of table1 and one column from Table2, repeated 3 times. That
was what confused me: the repetition of the column in the second table. In
fact, don't your 2 tables look more like the following?

Table1 Table 2
------------------------------
enteredbyID Contact_ID
CoordID
CustContactID

Now, assuming that you have a name for each Table2 Contact_ID, you would
indeed need a JOIN to ge t the name, and you wouldn't need a function to do
it. That is:

Table1 Table 2
------------------------------
enteredbyID Contact_ID
CoordID Contact_Name
CustContactID

In which case, you can execute the following SQL to get it:

SELECT Table1.enteredbyID AS enteredByID, Table1.CoordID AS CoordID,
Table2.Contact_Name AS Contact_Name
FROM Table1 INNER JOIN Table2 ON Table1.CustContactID = Table2.Contact_ID

This query would result in soomething ike the following result set:

enteredByID CoordID Contact_Name
1 123 John Doe
2 234 John Smith
3 345 Jane Doe

you could then bind that to your DataGrid without any DataBinding
expressions.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.

 

[Guest]

Thank you both for your input. I was able to integrate my function to change
the intergers (names) to first and last names. I did so in the ItemCreate sub
of the datagrid build:

e.Item.Cells(9).Text = qc2005s.GetName(enteredby)

I'll try your SQL approach when I have a less complex sql string. I couldn't
do it here given the time, this view has 7 interelated tables

Thanx again.