Table join question

[Chris]

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.

"Chris" <Ch***@discussi ons.microsoft.c om> wrote in message
news:7E******** *************** ***********@mic rosoft.com...

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.

[pj]

On Fri, 4 Mar 2005 09:21:05 -0800, "Chris"
<Ch***@discussi ons.microsoft.c om> wrote:

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
CustContactI D 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.CustCont actID refers to table2.ContactI D

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

SELECT table1.*, table2.FirstNam e, table2.LastName FROM table1
INNER JOIN table2 ON table1.CustCont actID = table2.ContactI D

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

--
http://www.psychedelicjones.com/

[Chris]

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:TemplateCo lumn HeaderText="Ass igned To"
HeaderStyle-CssClass="gridq text" ItemStyle-CssClass="gridt ext">
<ItemTemplate >
<asp:Label id="lblassigned to" width="150" runat="server" Text='<%#
DataBinder.Eval (Container.Data Item, "FullName") %>' Visible="True">
</asp:Label>
</ItemTemplate>
</asp:TemplateCol umn>

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.
"Kevin Spencer" wrote:

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.

"Chris" <Ch***@discussi ons.microsoft.c om> wrote in message
news:7E******** *************** ***********@mic rosoft.com...

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.

[Chris]

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" wrote:

On Fri, 4 Mar 2005 09:21:05 -0800, "Chris"
<Ch***@discussi ons.microsoft.c om> wrote:

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
CustContactI D 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.CustCont actID refers to table2.ContactI D

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

SELECT table1.*, table2.FirstNam e, table2.LastName FROM table1
INNER JOIN table2 ON table1.CustCont actID = table2.ContactI D

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

--
http://www.psychedelicjones.com/

[pj]

On Fri, 4 Mar 2005 10:02:24 -0800, "Chris"
<Ch***@discussi ons.microsoft.c om> wrote:

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:TemplateC olumn HeaderText="Ass igned To"
HeaderStyle-CssClass="gridq text" ItemStyle-CssClass="gridt ext">
<ItemTemplat e>
<asp:Label id="lblassigned to" width="150" runat="server" Text='<%#
DataBinder.Eva l(Container.Dat aItem, "FullName") %>' Visible="True">
</asp:Label>
</ItemTemplate>
</asp:TemplateCol umn>

I'm wondering if there is a simple way to use my function here in the grid:
qc2005s.GetNam e(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:enteredb yID:CoordID:Cus tContactID
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.First Name AS EnteredByFirstN ame,
EnteredBy.LastN ame AS EnteredByLastNa me,
Coord.FirstName AS CoordFirstName,
Coord.LastName AS CoordByLastName ,
CustContact.Fir stName AS CustContactFirs tName,
CustContact.Las tName AS CustContactLast Name
FROM table1 AS T
INNER JOIN table2 AS EnteredBy ON EnteredBy.Conta ctID = T.enteredbyID
INNER JOIN table2 AS Coord ON Coord.ContactID = T.coordID
INNER JOIN table2 AS CustContact ON CustContact.Con tactID =
T.CusrtcontactI D

and your results set just has the names you need.

pj
--
http://www.psychedelicjones.com/

[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:

> Table1 Table 2
> enteredbyID Contact_ID
> CoordID Contact_ID
> CustContactID Contact_ID

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.enteredb yID AS enteredByID, Table1.CoordID AS CoordID,
Table2.Contact_ Name AS Contact_Name
FROM Table1 INNER JOIN Table2 ON Table1.CustCont actID = 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.

"Chris" <Ch***@discussi ons.microsoft.c om> wrote in message
news:F2******** *************** ***********@mic rosoft.com... 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:TemplateCo lumn HeaderText="Ass igned To"
HeaderStyle-CssClass="gridq text" ItemStyle-CssClass="gridt ext">
<ItemTemplate >
<asp:Label id="lblassigned to" width="150" runat="server" Text='<%#
DataBinder.Eval (Container.Data Item, "FullName") %>' Visible="True">
</asp:Label>
</ItemTemplate>
</asp:TemplateCol umn>

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.
"Kevin Spencer" wrote:

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.

"Chris" <Ch***@discussi ons.microsoft.c om> wrote in message
news:7E******** *************** ***********@mic rosoft.com...

> 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.

[Chris]

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.

"pj" wrote:

On Fri, 4 Mar 2005 09:21:05 -0800, "Chris"
<Ch***@discussi ons.microsoft.c om> wrote:

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
CustContactI D 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.CustCont actID refers to table2.ContactI D

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

SELECT table1.*, table2.FirstNam e, table2.LastName FROM table1
INNER JOIN table2 ON table1.CustCont actID = table2.ContactI D

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

--
http://www.psychedelicjones.com/