Qustion about join 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
Wingthere is no join here but union.
e.g.
--assuming the columns have same datatype
--else you need to cast() them
select *
from (select CustomerID, CustomerName,TelNumber, 0 [i] from table1
union all
select CustomerID,ComponentPurchase,Quantity, 1 [i] from table2)derived
order by CustomerID,i
-oj
"Wing" <li.alwin@.gmail.com> wrote in message
news:1140828419.455283.57060@.i39g2000cwa.googlegroups.com...
> 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
>|||Thanks for the comment
i think your code should be enought to solve my problem.
thanks again
Wing

Questions on triggers and when they fire...

I have a stored proc that does something like 'Update table1 set a =
'b'
where c = 'd''
That works fine, no probs and updates multiple records in table1.
The problem I have is with the Update trigger. It only seems to fire
ONCE
(right at the end), regardless of how many rows the stored procedure
updated.
The only way around it that I can see is to only update one row at a
time in
the table and then the update trigger will work OK - which seems a bit
cumbersome
to me.
How can I get the Update trigger to fire for each row that is updated?
Thanks
Chris"How can I get the Update trigger to fire for each row that is updated?
"
-Short answer, you can=B4t. Triggers will fire on a statement basis NOT
on a row basis. Therefore all affected rows by the statement are
available in the inserted and deleted tables. You have to implement
setbased solution to handle all rows. If you execute a stored procedure
for each row you have to loop through the resultset (with using a temp
table or a (but not recommandable) cursor.
CREATE TRIGGER SomeTrigger
ON SomeTable
FOR UPDATE
AS
BEGIN
UPDATE SomeOtherTable
SET SomeColumn =3D INSERTED.SomeColumn
FROm SomeOtherTable
INNER JOIN
INSERTED.IDColumntoJoin
ON WSomeOtherTable.IDColumntoJoin =3D INSERTED.IDColumntoJoin
END
HTH, jens Suessmeyer.|||On 13 Jan 2006 03:03:23 -0800, Jens wrote:

>"How can I get the Update trigger to fire for each row that is updated?
>"
>-Short answer, you cant. Triggers will fire on a statement basis NOT
>on a row basis. Therefore all affected rows by the statement are
>available in the inserted and deleted tables. You have to implement
>setbased solution to handle all rows. If you execute a stored procedure
>for each row you have to loop through the resultset (with using a temp
>table or a (but not recommandable) cursor.
Hi Jens,
Much better suggestions for such a case are:
(Preferred) Rewrite the stored procedure's logic to set-based query and
insert that query in the trigger code.
(Or, in rare conditions) Rewrite the stored procedure to a set-based
procedure; have the trigger call this procedure after copying data from
inserted and deleted into temp tables.
Hugo Kornelis, SQL Server MVP

Questions on triggers and when they fire...

I have a stored proc that does something like 'Update table1 set a = 'b'
where c = 'd''
That works fine, no probs and updates multiple records in table1.
The problem I have is with the Update trigger. It only seems to fire
ONCE
(right at the end), regardless of how many rows the stored procedure
updated.
The only way around it that I can see is to only update one row at a
time in
the table and then the update trigger will work OK - which seems a bit
cumbersome
to me.
How can I get the Update trigger to fire for each row that is updated?
Thanks
Chris"How can I get the Update trigger to fire for each row that is updated?
"
-Short answer, you can=B4t. Triggers will fire on a statement basis NOT
on a row basis. Therefore all affected rows by the statement are
available in the inserted and deleted tables. You have to implement
setbased solution to handle all rows. If you execute a stored procedure
for each row you have to loop through the resultset (with using a temp
table or a (but not recommandable) cursor.
CREATE TRIGGER SomeTrigger
ON SomeTable
FOR UPDATE
AS
BEGIN
UPDATE SomeOtherTable
SET SomeColumn =3D INSERTED.SomeColumn
FROm SomeOtherTable
INNER JOIN
INSERTED.IDColumntoJoin
ON WSomeOtherTable.IDColumntoJoin =3D INSERTED.IDColumntoJoin
END
HTH, jens Suessmeyer.|||On 13 Jan 2006 03:03:23 -0800, Jens wrote:
>"How can I get the Update trigger to fire for each row that is updated?
>"
>-Short answer, you can´t. Triggers will fire on a statement basis NOT
>on a row basis. Therefore all affected rows by the statement are
>available in the inserted and deleted tables. You have to implement
>setbased solution to handle all rows. If you execute a stored procedure
>for each row you have to loop through the resultset (with using a temp
>table or a (but not recommandable) cursor.
Hi Jens,
Much better suggestions for such a case are:
(Preferred) Rewrite the stored procedure's logic to set-based query and
insert that query in the trigger code.
(Or, in rare conditions) Rewrite the stored procedure to a set-based
procedure; have the trigger call this procedure after copying data from
inserted and deleted into temp tables.
--
Hugo Kornelis, SQL Server MVP

Questions on triggers and when they fire...

I have a stored proc that does something like 'Update table1 set a =
'b'
where c = 'd''
That works fine, no probs and updates multiple records in table1.
The problem I have is with the Update trigger. It only seems to fire
ONCE
(right at the end), regardless of how many rows the stored procedure
updated.
The only way around it that I can see is to only update one row at a
time in
the table and then the update trigger will work OK - which seems a bit
cumbersome
to me.
How can I get the Update trigger to fire for each row that is updated?
Thanks
Chris
"How can I get the Update trigger to fire for each row that is updated?
"
-Short answer, you can=B4t. Triggers will fire on a statement basis NOT
on a row basis. Therefore all affected rows by the statement are
available in the inserted and deleted tables. You have to implement
setbased solution to handle all rows. If you execute a stored procedure
for each row you have to loop through the resultset (with using a temp
table or a (but not recommandable) cursor.
CREATE TRIGGER SomeTrigger
ON SomeTable
FOR UPDATE
AS
BEGIN
UPDATE SomeOtherTable
SET SomeColumn =3D INSERTED.SomeColumn
FROm SomeOtherTable
INNER JOIN
INSERTED.IDColumntoJoin
ON WSomeOtherTable.IDColumntoJoin =3D INSERTED.IDColumntoJoin
END
HTH, jens Suessmeyer.
|||On 13 Jan 2006 03:03:23 -0800, Jens wrote:

>"How can I get the Update trigger to fire for each row that is updated?
>"
>-Short answer, you cant. Triggers will fire on a statement basis NOT
>on a row basis. Therefore all affected rows by the statement are
>available in the inserted and deleted tables. You have to implement
>setbased solution to handle all rows. If you execute a stored procedure
>for each row you have to loop through the resultset (with using a temp
>table or a (but not recommandable) cursor.
Hi Jens,
Much better suggestions for such a case are:
(Preferred) Rewrite the stored procedure's logic to set-based query and
insert that query in the trigger code.
(Or, in rare conditions) Rewrite the stored procedure to a set-based
procedure; have the trigger call this procedure after copying data from
inserted and deleted into temp tables.
Hugo Kornelis, SQL Server MVP