Datagrid hide identical columns from a JOIN

I do not normally use JOINS in my queries these days, but I have some
instances where I am simply showing historical data in a grid, and it is
thus much more convenient to JOIN everything I need in one stored procedure.
Thus I might end up with data from 3, 4 or more tables -- some with
identical names (the JOIN criteria).

I know how to hide columns in a straightforward query, but how about in a
JOIN query where you may have 2 or even 3 or more identical field names (IDs
usually)? How do you hide, for example, the SalesID from the 2nd table or
the from the 3rd table? For example, the following statement works for the
first SalesID, but what about the other JOINED SalesID fields? (I've tried
"SalesID1" and "Expr1", but no joy except SalesID1 seems to work on a simple
query with 2 joined tables).

ds.Tables("dtSalesPayments").Columns("SalesID").ColumnMapping =
MappingType.Hidden

v-kevy
Thu Jun 17 03:51:21 CDT 2004

Hi Earl,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to hide or make difference
between the field with the same name in a joint table. If there is any
misunderstanding, please feel free to let me know.

There are many way to resolved this issue. If you don't need to show these
fields, such as SalesID, you needn't to include them in the SELECT
statement. For example, I need to hide EmployeeID when joining:

SELECT Orders.OrderID, Orders.OrderDate, Employees.FirstName FROM Orders
LEFT JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID

This is recommended. Or you can select the column and fill them into a
DataSet and using ColumnMapping to hide that column. The duplicated field
will be renamed automatically to "SalesID1".

ds.Tables("dtSalesPayments").Columns("SalesID").ColumnMapping =
MappingType.Hidden
ds.Tables("dtSalesPayments").Columns("SalesID1").ColumnMapping =
MappingType.Hidden

If you need to give the ID columns with different name, you can achieve
this by using AS in the SQL statement. Here is an example:

SELECT Orders.OrderID, Orders.OrderDate, Employees.FirstName,
Orders.EmployeeID AS OrdEmpID, Employees.EmployeeID AS EmpEmpID FROM Orders
LEFT JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Earl
Thu Jun 17 10:19:44 CDT 2004

Thanks Kevin, that works.

"Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in message
news:I2$zLhEVEHA.2764@cpmsftngxa10.phx.gbl...
> Hi Earl,
>
> First of all, I would like to confirm my understanding of your issue. From
> your description, I understand that you need to hide or make difference
> between the field with the same name in a joint table. If there is any
> misunderstanding, please feel free to let me know.
>
> There are many way to resolved this issue. If you don't need to show these
> fields, such as SalesID, you needn't to include them in the SELECT
> statement. For example, I need to hide EmployeeID when joining:
>
> SELECT Orders.OrderID, Orders.OrderDate, Employees.FirstName FROM Orders
> LEFT JOIN Employees
> ON Orders.EmployeeID = Employees.EmployeeID
>
> This is recommended. Or you can select the column and fill them into a
> DataSet and using ColumnMapping to hide that column. The duplicated field
> will be renamed automatically to "SalesID1".
>
> ds.Tables("dtSalesPayments").Columns("SalesID").ColumnMapping =
> MappingType.Hidden
> ds.Tables("dtSalesPayments").Columns("SalesID1").ColumnMapping =
> MappingType.Hidden
>
> If you need to give the ID columns with different name, you can achieve
> this by using AS in the SQL statement. Here is an example:
>
> SELECT Orders.OrderID, Orders.OrderDate, Employees.FirstName,
> Orders.EmployeeID AS OrdEmpID, Employees.EmployeeID AS EmpEmpID FROM
Orders
> LEFT JOIN Employees
> ON Orders.EmployeeID = Employees.EmployeeID
>
> HTH.
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>

gh0st54
Thu Jun 17 15:10:29 CDT 2004

Hi

Why don't you write your query to return only the fields that you need
?

let's say you have SalesId in sales table and SalesId in salesdetails
table
you will join on salesid but only want the id to be displayed once.

the query would be :
select sales.[salesid],salesdetails.[column_name] from sales inner
join salesdetails on sales.salesid = salesdetails.salesid

I hope that this is what you are looking for, this way the query is
faster (retrieves only the data that you need) and you don't have
redundant data.

gh0st54

"Earl" <brikshoe@newsgroups.nospam> wrote in message news:<#KC1PfDVEHA.3428@TK2MSFTNGP12.phx.gbl>...
> I do not normally use JOINS in my queries these days, but I have some
> instances where I am simply showing historical data in a grid, and it is
> thus much more convenient to JOIN everything I need in one stored procedure.
> Thus I might end up with data from 3, 4 or more tables -- some with
> identical names (the JOIN criteria).
>
> I know how to hide columns in a straightforward query, but how about in a
> JOIN query where you may have 2 or even 3 or more identical field names (IDs
> usually)? How do you hide, for example, the SalesID from the 2nd table or
> the from the 3rd table? For example, the following statement works for the
> first SalesID, but what about the other JOINED SalesID fields? (I've tried
> "SalesID1" and "Expr1", but no joy except SalesID1 seems to work on a simple
> query with 2 joined tables).
>
> ds.Tables("dtSalesPayments").Columns("SalesID").ColumnMapping =
> MappingType.Hidden

Earl
Thu Jun 17 15:35:11 CDT 2004

From previous experience (maybe on Access), I was under the impression I
couldn't JOIN on table.columns I didn't Select. I'll have to jump in and try
that on SQL2k.

"gh0st54" <gh0st54@hotmail.com> wrote in message
news:7fa3be06.0406171210.1694fa15@posting.google.com...
> Hi
>
> Why don't you write your query to return only the fields that you need
> ?
>
> let's say you have SalesId in sales table and SalesId in salesdetails
> table
> you will join on salesid but only want the id to be displayed once.
>
> the query would be :
> select sales.[salesid],salesdetails.[column_name] from sales inner
> join salesdetails on sales.salesid = salesdetails.salesid
>
> I hope that this is what you are looking for, this way the query is
> faster (retrieves only the data that you need) and you don't have
> redundant data.
>
> gh0st54
>
> "Earl" <brikshoe@newsgroups.nospam> wrote in message
news:<#KC1PfDVEHA.3428@TK2MSFTNGP12.phx.gbl>...
> > I do not normally use JOINS in my queries these days, but I have some
> > instances where I am simply showing historical data in a grid, and it is
> > thus much more convenient to JOIN everything I need in one stored
procedure.
> > Thus I might end up with data from 3, 4 or more tables -- some with
> > identical names (the JOIN criteria).
> >
> > I know how to hide columns in a straightforward query, but how about in
a
> > JOIN query where you may have 2 or even 3 or more identical field names
(IDs
> > usually)? How do you hide, for example, the SalesID from the 2nd table
or
> > the from the 3rd table? For example, the following statement works for
the
> > first SalesID, but what about the other JOINED SalesID fields? (I've
tried
> > "SalesID1" and "Expr1", but no joy except SalesID1 seems to work on a
simple
> > query with 2 joined tables).
> >
> > ds.Tables("dtSalesPayments").Columns("SalesID").ColumnMapping =
> > MappingType.Hidden

Earl
Sat Jun 19 18:24:51 CDT 2004

A couple of things I was working on today got me to thinking about this
issue. There are a couple of other issues with not retrieving your primary
keys: One is that your currencymanager will cause an exception when you try
to delete if you have not recalled your original value columns such as your
primary key. I found this out the hard way tweaking my stored procedures to
remove what I thought was "extraneous" fields. The second issue is that
failing to make a Select without an indexed field will also cause
degredation in your performance. Life is a tradeoff.

"gh0st54" <gh0st54@hotmail.com> wrote in message
news:7fa3be06.0406171210.1694fa15@posting.google.com...
> Hi
>
> Why don't you write your query to return only the fields that you need
> ?
>
> let's say you have SalesId in sales table and SalesId in salesdetails
> table
> you will join on salesid but only want the id to be displayed once.
>
> the query would be :
> select sales.[salesid],salesdetails.[column_name] from sales inner
> join salesdetails on sales.salesid = salesdetails.salesid
>
> I hope that this is what you are looking for, this way the query is
> faster (retrieves only the data that you need) and you don't have
> redundant data.
>
> gh0st54
>
> "Earl" <brikshoe@newsgroups.nospam> wrote in message
news:<#KC1PfDVEHA.3428@TK2MSFTNGP12.phx.gbl>...
> > I do not normally use JOINS in my queries these days, but I have some
> > instances where I am simply showing historical data in a grid, and it is
> > thus much more convenient to JOIN everything I need in one stored
procedure.
> > Thus I might end up with data from 3, 4 or more tables -- some with
> > identical names (the JOIN criteria).
> >
> > I know how to hide columns in a straightforward query, but how about in
a
> > JOIN query where you may have 2 or even 3 or more identical field names
(IDs
> > usually)? How do you hide, for example, the SalesID from the 2nd table
or
> > the from the 3rd table? For example, the following statement works for
the
> > first SalesID, but what about the other JOINED SalesID fields? (I've
tried
> > "SalesID1" and "Expr1", but no joy except SalesID1 seems to work on a
simple
> > query with 2 joined tables).
> >
> > ds.Tables("dtSalesPayments").Columns("SalesID").ColumnMapping =
> > MappingType.Hidden