Needs Help !

Hi all
I have the following two tables
Table #1
Item No Name
1 Alfa
2 Bravo
3 Charlie
4 Delta
Table #2
Sr No Item No Item Name Shipped Time
1 1 Alfa 10 Mins
2 1 Alfa 20 Mins
3 2 Bravo 50 Mins
4 1 Alfa 40 Mins
5 1 Alfa 30 Mins
6 3 Charlie 30 Mins
7 4 Delta 20 Mins
8 4 Delta 10 Mins
There is a relation ship between the two tables on Item No
Can somebody help me in writing the query to get following output
Item Name Sr No Shipped Time
Alfa 1 10 Min
2 20 Min
4 40 Min
5 30 Min
Bravo 3 50 Min
Charlie 6 30 Min
Delta 7 20 Min
8 10 Min
Best Regards
SierraHi
This is an idea which was given by Steve Kass.Modify it for your needs
select CustomerID, OrderID from (
select
CustomerID as Position_1,
1 as Position_2,
CustomerID,
cast(OrderID as varchar(15)) as OrderID
from Northwind..Orders
union all
select distinct
CustomerID,
2,
'',
N''
from Northwind..Orders
) Report
order by Position_1, Position_2
go
"Sierra" <senthilvel_sundaram@.yahoo.com> wrote in message news:urBbgdGKFHA.4
84@.TK2MSFTNGP15.phx.gbl...
Hi all
I have the following two tables
Table #1
Item No Name
1 Alfa
2 Bravo
3 Charlie
4 Delta
Table #2
Sr No Item No Item Name Shipped Time
1 1 Alfa 10 Mins
2 1 Alfa 20 Mins
3 2 Bravo 50 Mins
4 1 Alfa 40 Mins
5 1 Alfa 30 Mins
6 3 Charlie 30 Mins
7 4 Delta 20 Mins
8 4 Delta 10 Mins
There is a relation ship between the two tables on Item No
Can somebody help me in writing the query to get following output
Item Name Sr No Shipped Time
Alfa 1 10 Min
2 20 Min
4 40 Min
5 30 Min
Bravo 3 50 Min
Charlie 6 30 Min
Delta 7 20 Min
8 10 Min
Best Regards
Sierra|||I guess this query should solve your problem
item --> Table #1
iShipped --> Table #2
select i.Name, s.SrNo, s.[Shipped Time]
from item i inner join iShipped s on i.[item no] = s.[item no]
order by i.Name
Krish
"Sierra" <senthilvel_sundaram@.yahoo.com> wrote in message
news:urBbgdGKFHA.484@.TK2MSFTNGP15.phx.gbl...
Hi all
I have the following two tables
Table #1
Item No Name
1 Alfa
2 Bravo
3 Charlie
4 Delta
Table #2
Sr No Item No Item Name Shipped Time
1 1 Alfa 10 Mins
2 1 Alfa 20 Mins
3 2 Bravo 50 Mins
4 1 Alfa 40 Mins
5 1 Alfa 30 Mins
6 3 Charlie 30 Mins
7 4 Delta 20 Mins
8 4 Delta 10 Mins
There is a relation ship between the two tables on Item No
Can somebody help me in writing the query to get following output
Item Name Sr No Shipped Time
Alfa 1 10 Min
2 20 Min
4 40 Min
5 30 Min
Bravo 3 50 Min
Charlie 6 30 Min
Delta 7 20 Min
8 10 Min
Best Regards
Sierra|||Sireea,
Ideally this should be done at the client side. Still if you have some compe
lling reasons..
CREATE TABLE #Table1(ItemNo int, ItemName VARCHAR(10))
INSERT INTO #Table1 VALUES(1,'Alfa')
INSERT INTO #Table1 VALUES(2,'Bravo')
INSERT INTO #Table1 VALUES(3,'Charlie')
INSERT INTO #Table1 VALUES(4,'Delta')
CREATE TABLE #Table2(SrNo int, ItemNo int, Shippedin int)
INSERT INTO #Table2 VALUES(1,1,10)
INSERT INTO #Table2 VALUES(2,1,20)
INSERT INTO #Table2 VALUES(3,2,50)
INSERT INTO #Table2 VALUES(4,1,40)
INSERT INTO #Table2 VALUES(5,1,30)
INSERT INTO #Table2 VALUES(6,3,30)
INSERT INTO #Table2 VALUES(7,4,20)
INSERT INTO #Table2 VALUES(8,4,10)
SELECT CASE WHEN Exists(SELECT 1 FROM #Table2 T WHERE B.SrNo > T.SrNo AND A
.ItemNo = T.ItemNo)
THEN '' ELSE A.ItemName END As ItemName,
SrNo, Shippedin
FROM #Table1 A
INNER JOIN #Table2 B
ON A.ItemNo = B.ItemNo
ORDER By A.ItemNo
--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Sierra" <senthilvel_sundaram@.yahoo.com> wrote in message news:urBbgdGKFHA.4
84@.TK2MSFTNGP15.phx.gbl...
Hi all
I have the following two tables
Table #1
Item No Name
1 Alfa
2 Bravo
3 Charlie
4 Delta
Table #2
Sr No Item No Item Name Shipped Time
1 1 Alfa 10 Mins
2 1 Alfa 20 Mins
3 2 Bravo 50 Mins
4 1 Alfa 40 Mins
5 1 Alfa 30 Mins
6 3 Charlie 30 Mins
7 4 Delta 20 Mins
8 4 Delta 10 Mins
There is a relation ship between the two tables on Item No
Can somebody help me in writing the query to get following output
Item Name Sr No Shipped Time
Alfa 1 10 Min
2 20 Min
4 40 Min
5 30 Min
Bravo 3 50 Min
Charlie 6 30 Min
Delta 7 20 Min
8 10 Min
Best Regards
Sierra