Datagrid hide identical columns from a JOIN

  • Thread starter Thread starter Earl
  • Start date Start date
E

Earl

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
 
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."
 
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
 
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 said:
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" <[email protected]> wrote in message
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
Click to expand...
Click to expand...
 
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 said:
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" <[email protected]> wrote in message
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
Click to expand...
Click to expand...
 
Back
Top