Question about joining 2 tables??

Hi everyone,
I have problem to join 2 tables together to show the selected results
into a datagrid.
table1 hosts all customer personal information.
table2 hosts all the trasaction records for each customer
table1 has fields such as "customerID", "CustomerName" and "TelNumber".
(customerID is primary key in table1)
table2 has fields such as "cusomterID", "ComponentPurchase" and
"Quantity"
table1
CustomerID CustomerName TelNumber
55 John 1234566
56 David 6589211
table2
CustomerID ComponentPurchase Quantity
55 componentA 10
55 componentB 5
55 componentC 1
56 componentA 2
how can i join these two tables and get the result datagrid that list
each customer personal information
and the component he/she purchase listed below his/her personal
information' such as look like following
one
John 1234566
componentA 10
componentB 5
componentC 1
David 6589211
componentA 2
Could everyone give me some suggestion, or any article i can read?
thanks for your time
WingWing wrote:
> Hi everyone,
> I have problem to join 2 tables together to show the selected results
> into a datagrid.
> table1 hosts all customer personal information.
> table2 hosts all the trasaction records for each customer
> table1 has fields such as "customerID", "CustomerName" and
> "TelNumber". (customerID is primary key in table1)
> table2 has fields such as "cusomterID", "ComponentPurchase" and
> "Quantity"
> table1
> CustomerID CustomerName TelNumber
> 55 John 1234566
> 56 David 6589211
>
> table2
> CustomerID ComponentPurchase Quantity
> 55 componentA 10
> 55 componentB 5
> 55 componentC 1
> 56 componentA 2
>
> how can i join these two tables and get the result datagrid that list
> each customer personal information
> and the component he/she purchase listed below his/her personal
> information' such as look like following
> one
> John 1234566
> componentA 10
> componentB 5
> componentC 1
> David 6589211
> componentA 2
> Could everyone give me some suggestion, or any article i can read?
> thanks for your time
> Wing
This is a UI design issue and not a SQL Server one. I assume you know
how to write a SELECT statement that joins two tables together. How that
information is evenutally displays in the UI depends on what controls
you are using and your code. This is not really a SQL issue. Many data
grids support a parent-child style layout, but that's a question for
another group.
David Gugick - SQL Server MVP
Quest Software|||Thanks for your comment
Wing

Question about joining 2 tables??

Hi everyone,
I have problem to join 2 tables together to show the selected results
into a datagrid.
table1 hosts all customer personal information.
table2 hosts all the trasaction records for each customer
table1 has fields such as "customerID", "CustomerName" and "TelNumber".
(customerID is primary key in table1)
table2 has fields such as "cusomterID", "ComponentPurchase" and
"Quantity"
table1
CustomerID CustomerName TelNumber
55 John 1234566
56 David 6589211
table2
CustomerID ComponentPurchase Quantity
55 componentA 10
55 componentB 5
55 componentC 1
56 componentA 2
how can i join these two tables and get the result datagrid that list
each customer personal information
and the component he/she purchase listed below his/her personal
information' such as look like following
one
John 1234566
componentA 10
componentB 5
componentC 1
David 6589211
componentA 2
Could everyone give me some suggestion, or any article i can read?
thanks for your time
WingWing wrote:
> Hi everyone,
> I have problem to join 2 tables together to show the selected results
> into a datagrid.
> table1 hosts all customer personal information.
> table2 hosts all the trasaction records for each customer
> table1 has fields such as "customerID", "CustomerName" and
> "TelNumber". (customerID is primary key in table1)
> table2 has fields such as "cusomterID", "ComponentPurchase" and
> "Quantity"
> table1
> CustomerID CustomerName TelNumber
> 55 John 1234566
> 56 David 6589211
>
> table2
> CustomerID ComponentPurchase Quantity
> 55 componentA 10
> 55 componentB 5
> 55 componentC 1
> 56 componentA 2
>
> how can i join these two tables and get the result datagrid that list
> each customer personal information
> and the component he/she purchase listed below his/her personal
> information' such as look like following
> one
> John 1234566
> componentA 10
> componentB 5
> componentC 1
> David 6589211
> componentA 2
> Could everyone give me some suggestion, or any article i can read?
> thanks for your time
> Wing
This is a UI design issue and not a SQL Server one. I assume you know
how to write a SELECT statement that joins two tables together. How that
information is evenutally displays in the UI depends on what controls
you are using and your code. This is not really a SQL issue. Many data
grids support a parent-child style layout, but that's a question for
another group.
--
David Gugick - SQL Server MVP
Quest Software|||Thanks for your comment
Wing

Question about joining 2 tables??

Hi everyone,
I have problem to join 2 tables together to show the selected results
into a datagrid.
table1 hosts all customer personal information.
table2 hosts all the trasaction records for each customer
table1 has fields such as "customerID", "CustomerName" and "TelNumber".
(customerID is primary key in table1)
table2 has fields such as "cusomterID", "ComponentPurchase" and
"Quantity"
table1
CustomerID CustomerName TelNumber
55 John 1234566
56 David 6589211
table2
CustomerID ComponentPurchase Quantity
55 componentA 10
55 componentB 5
55 componentC 1
56 componentA 2
how can i join these two tables and get the result datagrid that list
each customer personal information
and the component he/she purchase listed below his/her personal
information? such as look like following
one
John 1234566
componentA 10
componentB 5
componentC 1
David 6589211
componentA 2
Could everyone give me some suggestion, or any article i can read?
thanks for your time
Wing
Wing wrote:
> Hi everyone,
> I have problem to join 2 tables together to show the selected results
> into a datagrid.
> table1 hosts all customer personal information.
> table2 hosts all the trasaction records for each customer
> table1 has fields such as "customerID", "CustomerName" and
> "TelNumber". (customerID is primary key in table1)
> table2 has fields such as "cusomterID", "ComponentPurchase" and
> "Quantity"
> table1
> CustomerID CustomerName TelNumber
> 55 John 1234566
> 56 David 6589211
>
> table2
> CustomerID ComponentPurchase Quantity
> 55 componentA 10
> 55 componentB 5
> 55 componentC 1
> 56 componentA 2
>
> how can i join these two tables and get the result datagrid that list
> each customer personal information
> and the component he/she purchase listed below his/her personal
> information? such as look like following
> one
> John 1234566
> componentA 10
> componentB 5
> componentC 1
> David 6589211
> componentA 2
> Could everyone give me some suggestion, or any article i can read?
> thanks for your time
> Wing
This is a UI design issue and not a SQL Server one. I assume you know
how to write a SELECT statement that joins two tables together. How that
information is evenutally displays in the UI depends on what controls
you are using and your code. This is not really a SQL issue. Many data
grids support a parent-child style layout, but that's a question for
another group.
David Gugick - SQL Server MVP
Quest Software
|||Thanks for your comment
Wing

Question about Joining 2 tables?

Hi everyone,

I have problem to join 2 tables together to show the selected results
into a datagrid.

table1 hosts all customer personal information.
table2 hosts all the trasaction records for each customer

table1 has fields such as "customerID", "CustomerName" and "TelNumber".
(customerID is primary key in table1)

table2 has fields such as "cusomterID", "ComponentPurchase" and
"Quantity"

table1

CustomerID CustomerName TelNumber
55 John 1234566
56 David 6589211

table2

CustomerID ComponentPurchase Quantity
55 componentA 10
55 componentB 5
55 componentC 1
56 componentA 2

how can i join these two tables and get the result datagrid that list
each customer personal information and the component he/she purchased
listed below his/her personal information?? such as look like following
one

--------------
John 1234566
componentA 10
componentB 5
componentC 1
--------------
David 6589211
componentA 2
--------------

Could everyone give me some suggestion, or any article i can read?

thanks for your time

Wing"Wing" <li.alwin@.gmail.com> wrote in news:1140828715.240420.308800
@.u72g2000cwu.googlegroups.com:

> Hi everyone,
> I have problem to join 2 tables together to show the selected results
> into a datagrid.
>
> Could everyone give me some suggestion, or any article i can read?

My suggestion:

Do your own homework. You'll learn more from the class that way.|||I am doing my own homework, just need some knowledge that to start my
work.
thanks for comment.sql

Question about Database Collations

I sent a couple of detached databases to a customer. When he installed his SQL Server he got database collation value of Latin1_General_CI_AS however, when he attached my databases they have a collation value of SQL_Latin1_General_CPI_CI_AS.

Now my stored procedures that use temp files are failing and a message is coming out SQL Server "Cannot resolve collation conflict for equal to operation"

I am assuming that the temp database has one collation value and my databases has another.

Q. Can anyone tell me the difference between the two collations? He is in Australia, I am in Canada - would he get a different default than I do?

Q. Can I safely alter the collation sequence of either the databases I sent or the temp database so that they match?

I have never run across this before but this is the first time I shipped the database offshore.

Thanks for any help you can give me. I am going to have to send my database to the Caribbean pretty soon and I need to know if this is going to happen there as well.

Well now that I've solved the problem I wonder what is the reasoning behind the defaults chosen by install.

First I can find no provision in the SQL Install to change the Default collation value.

My customer in Australia installed SQL Server on a new installation of Windows Server 2003 and got Latin1_General_CI_AS.
I built a brand new instance of Windows Server 2003 and when I installed SQL I got SQL_Latin1_General_CP1_CI_AS

The problem with my databases was in the stored procedures that used temporary tables - the collation values were different in the two databases and any comparisons on character fields failed.

I can work around the problem by altering all the stored procedures to include the collation clause to override the collation of tempdb. Is this the best way? I'm not sure. Is it a good practice to always include the collation clause when defining a table? I've always looked at it as kind of an annoyance -- never again
|||

I am replying to this fairly old post because others may be wondering why they get the SQL_Latin1_General_CP1_CI_AS collation.

I believe that this is the 'old fashioned' collation used by versions of SQL Server prior to 2000. It seems to be offered on upgrade, whereas Latin1_General_CI_AS is used in from scratch installations. Certainly the latter is preferred.

I'm disappointed that SQL2005 doesn't have a change collation wizard and you need to create a DMO script in order to change the collation of existing databases.

|||

did you know which were the installation parameters you choose to get SQL_Latin1_General_CP1_CI_AS?

thanks for your help

|||

Andres,

Upon installation, you need to select "Collation designator and sort order:"
Set it to "Latin1_General"
then, check the box for "Accent - sensitive"

That should do it!

Question about Database Collations

I sent a couple of detached databases to a customer. When he installed his SQL Server he got database collation value of Latin1_General_CI_AS however, when he attached my databases they have a collation value of SQL_Latin1_General_CPI_CI_AS.

Now my stored procedures that use temp files are failing and a message is coming out SQL Server "Cannot resolve collation conflict for equal to operation"

I am assuming that the temp database has one collation value and my databases has another.

Q. Can anyone tell me the difference between the two collations? He is in Australia, I am in Canada - would he get a different default than I do?

Q. Can I safely alter the collation sequence of either the databases I sent or the temp database so that they match?

I have never run across this before but this is the first time I shipped the database offshore.

Thanks for any help you can give me. I am going to have to send my database to the Caribbean pretty soon and I need to know if this is going to happen there as well.

Well now that I've solved the problem I wonder what is the reasoning behind the defaults chosen by install.

First I can find no provision in the SQL Install to change the Default collation value.

My customer in Australia installed SQL Server on a new installation of Windows Server 2003 and got Latin1_General_CI_AS.
I built a brand new instance of Windows Server 2003 and when I installed SQL I got SQL_Latin1_General_CP1_CI_AS

The problem with my databases was in the stored procedures that used temporary tables - the collation values were different in the two databases and any comparisons on character fields failed.

I can work around the problem by altering all the stored procedures to include the collation clause to override the collation of tempdb. Is this the best way? I'm not sure. Is it a good practice to always include the collation clause when defining a table? I've always looked at it as kind of an annoyance -- never again
|||

I am replying to this fairly old post because others may be wondering why they get the SQL_Latin1_General_CP1_CI_AS collation.

I believe that this is the 'old fashioned' collation used by versions of SQL Server prior to 2000. It seems to be offered on upgrade, whereas Latin1_General_CI_AS is used in from scratch installations. Certainly the latter is preferred.

I'm disappointed that SQL2005 doesn't have a change collation wizard and you need to create a DMO script in order to change the collation of existing databases.

|||

did you know which were the installation parameters you choose to get SQL_Latin1_General_CP1_CI_AS?

thanks for your help

|||

Andres,

Upon installation, you need to select "Collation designator and sort order:"
Set it to "Latin1_General"
then, check the box for "Accent - sensitive"

That should do it!

Question - I think it has got to do with grouping (although not su

Hello,
I have created a list in visual studio reporting services with all
outstanding invoices for a range of customers (where customer number between
'From' and 'To' parameter). I have also created a statement in the reporting
layout, but now I would like to know I can make sure that the statement for
every customer appears on a new page?
Statement looks as follows:
---
Customer number: XXXXXXXXXX
Customer name: John Doe
Adress: Test street 23
...
======= Invoice details (this is a table which contains all outstanding invoices for
this customer including the open amounts).
Please help.
Thanks!
CheersOn Jan 10, 3:19 am, DHL <D...@.discussions.microsoft.com> wrote:
> Hello,
> I have created a list in visual studio reporting services with all
> outstanding invoices for a range of customers (where customer number between
> 'From' and 'To' parameter). I have also created a statement in the reporting
> layout, but now I would like to know I can make sure that the statement for
> every customer appears on a new page?
> Statement looks as follows:
> ---
> Customer number: XXXXXXXXXX
> Customer name: John Doe
> Adress: Test street 23
> ...
> =======> Invoice details (this is a table which contains all outstanding invoices for
> this customer including the open amounts).
> Please help.
> Thanks!
> Cheers
You would set your Group properties to 'Page break at end.' You would
do this via: right-clicking the table/matrix control >> select
Properties >> select Groups tab >> select the Edit... button >> then
select 'Page break at end.' Hope this helps.
Regards,
Enrique Martinez
Sr Software Consultant